July 20, 2010 at 2:48 pm
Over the weekend we migrated SQL Server 2000 System and User databases from a Windows 2000 server to a Windows 2003 server, also running SQL Server 2000, which was then renamed back to the Windows 2000 server's server name. In the above process, we moved Model and MSDB to new locations on the new server. We did a dropserver/addserver to put the original name back.
Now we are having the following problems:
Non-SA's are getting
Error 229: execute permission denied on object 'xp_sqlagent_enum_jobs', database 'master', owner 'dbo'.
when they try to access Jobs on DBSI15 in Enterprise Manager/SQL Server Agent. Does anyone know what could be causing this issue?
October 20, 2010 at 10:17 am
I searched and saw a lot of things about certificates, but that was not my particular problem. I checked the certificates on msdb and master, and they were identical.
I noticed that the ##MS_AgentSigningCertificate## user was missing from the master database. After searching for a sophisticated way to bring it back, I resorted to this, which worked
CREATE USER [##MS_AgentSigningCertificate##] FOR LOGIN [##MS_AgentSigningCertificate##]
GO
GRANT EXECUTE TO [##MS_AgentSigningCertificate##]
go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply