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
Costa Rica
Technorati Tags: SQL Server
LiveJournal Tags: SQL Server
del.icio.us Tags: SQL Server
Comments