Execute permission denied SQL 2008
I’ve been trying to clean up our permissions on our dev, qa, and production boxes before we “cut-over” to our new website. I created our QA members a nifty new login with all the correct perms in dev. My co-workerthen pushed the new permission to QA, and guess what? It doesn’t work. I manually created a new user with the same set up as dev and it still will not work correctly. I need our QA members to see our sql jobs and be able to view them, but not be able to actually run the jobs. Here is the error I was seeing:
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)
I found a lot of suggestions out there saying to manually grant exec perms to that user, but if I didn’t have to do that in dev, why would I do that in QA?
I finally came across this solution from Microsoft, and this is when I remembered that MSDB had been backed up and restored on QA from Dev (months in the past). I ran this query and it worked, ta-da, my permissions are now set up correctly and they work!
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 SIDCREATE 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