SQL 2008 Error al restaurar la bd MSDB de una instancia a otra

Si haces un respaldo de la base de datos MSDB y la recuperas en otra instancia puede que se presente este error cuando alguien con pocos privilegios intenta ver los paquetes de jobs en el Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

 

Para solucionarlo MS recomienda ejecutar el siguiente script

use msdb
go
-- Backup the Agent certificate from the remote server to a file
BACKUP CERTIFICATE [##MS_AgentSigningCertificate##] TO FILE = 'MS_AgentSigningCertificate.remote_server.cer'
go
use master
go
-- re-create the agent certificate on master
-- Note: Because we are making these changes using a regular user and not as part of setup, the name
-- cannot include the ## token.
-- Creating a regular certificate in this case should be the equivalent as we only need it to derive a SID

CREATE CERTIFICATE [MS_AgentSigningCertificate.remote_server] FROM FILE = 'MS_AgentSigningCertificate.remote_server.cer'
go
-- Recreate the user mapped to the cert and grant the same permissions that the regular certificate needs.
CREATE USER [MS_AgentSigningCertificate.remote_server] FROM CERTIFICATE [MS_AgentSigningCertificate.remote_server]
go
GRANT EXECUTE TO [MS_AgentSigningCertificate.remote_server]
go

http://support.microsoft.com/default.aspx?scid=kb;en-us;2000274&sd=rss&spid=13165

Saludos,

Eduardo Castro – Microsoft SQL Server MVP

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server