SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database...


The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.

Author
Message
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
Problem Statement:
I have some users that are not a part of any server role (except the default public). For each of the user databases their permissions are tuned to deal only with user objects (tables, procedures, views, etc - standard read, write, execute, etc). We have no issue with their permissions as far as it relates to any of the user databases. However they also need the ability to administer SQL Agent jobs so I added them as users (USE [msdb]; CREATE USER <login> FOR LOGIN <login>Wink and then added them to the SQLAgentOperatorRole.

Now when these users attempt to expand the SQL Server Agent --> Jobs node in SSMS, they receive the error The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'..

I have Googled and Binged and found two possible solutions - (a) follow the KB article http://support.microsoft.com/kb/2000274 and (b) add the users to the master database.

This raises two situations for me. First, I do not fully follow the KB article since not all the instances this problem exists are due to restored databases. Second, I do not like the idea of giving my users access to the master database. I did do a test and added the users to the master database and gave them db_owner permissions. After doing this, the error went away.

My question is, does anyone else have experience with this error and what solution do you prefer (one of the above or even a different one).
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
No opinions?
Silverfox
Silverfox
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3464 Visits: 1161
hmm interesting,

I had a sql login called test, added it as a user to msdb, added it to the sqlagentoperator role and it can view the jobs and edit the jobs just fine.

what is the build version of your sql instance?

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
We have seven servers total but these users in question only have access to 3 of the servers:
DEV - Version: 9.00.3042.00, SP2, Developer Edition
QA - Version: 9.00.3042.00, SP2, Enterprise Edition
Staging - Version: 9.00.3042.00, SP2, Enterprise Edition

Note all are 32 bit.

What version are you on and can you identify the permissions your test user has for all databases? I have two scripts that can help you with that (see attached). One is for server permissions, the other is for individual database permissions. This way if SQL Server is doing something under the hood this should uncover that and I can compare the permissions to my users.

I personally wonder if it is tied the the certificate. I inherited the databases and it appears some of the ## system accounts have been dropped.
Attachments
AuditPermissions.zip (36 views, 3.00 KB)
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
Ok, I have an update and a good idea what the issue is. Since our base issue is users that are part of the SQLAgentOperatorRole are unable to view/edit SQL Agent jobs (as the original error or a form of it is generated), I created the following script to test this out on several servers (7 in total). 3 of the servers generate the error, 4 did not.

SET NOCOUNT ON;
GO

/* Fully remove the test server principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [sys].[server_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [master]; DROP LOGIN [SQLAgentTest];
END
/* Fully remove the test database principal if it exists */
IF EXISTS (SELECT TOP 1 1 FROM [msdb].[sys].[database_principals] WHERE name = 'SQLAgentTest')
BEGIN
USE [msdb]; DROP USER [SQLAgentTest];
END
GO

/* Create base account with base permissions */
USE [master]; CREATE LOGIN [SQLAgentTest] WITH PASSWORD = 'someRandomValue', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
USE [msdb]; CREATE USER [SQLAgentTest] FOR LOGIN [SQLAgentTest];
USE [msdb]; EXEC sp_addrolemember @rolename='SQLAgentOperatorRole', @membername='SQLAgentTest';
GO

/* Now test and see if the error is raised */
USE [msdb]; EXECUTE AS LOGIN='SQLAgentTest';
GO
USE [msdb]; EXEC [msdb].[dbo].[sp_help_job];
GO
USE [msdb]; REVERT;
GO

/* Remove the principals since testing was completed */
USE [master]; DROP LOGIN [SQLAgentTest];
USE [msdb]; DROP USER [SQLAgentTest];
GO



The error that is generated is close to something like

Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.


The exact error depends on the action the user is performing and the only thing that changes is the object name in the error.

Now, I'm comparing the ##MS_AgentSigningCertificate## certificate in master and msdb using this query:

SELECT 'master', *
FROM master.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
UNION
SELECT 'msdb', *
FROM msdb.sys.certificates
WHERE name = '##MS_AgentSigningCertificate##'
ORDER BY 1;



On the 4 servers that generate an error, these certificates are not the same. On the 3 servers that do work, these certificates are the same. I'm guessing SQL Server uses this internally to apply the necessary permissions. My question is, how do I fix these certificates? Thoughts?
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10004 Visits: 5314
Do any of the ones that fail happen to be restored from somewhere else? Are you having any other securiy problems on those databases? Can you setup proxy users? I'm guessing you can't setup proxy users because it can't encrypt the credentials..

CEWII
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10004 Visits: 5314
Also, in reference to that KB article, you might just want to do it anyway..

CEWII
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
Elliott W (10/6/2009)
Do any of the ones that fail happen to be restored from somewhere else? Are you having any other securiy problems on those databases? Can you setup proxy users? I'm guessing you can't setup proxy users because it can't encrypt the credentials..

CEWII


This is the only known issue at the moment. And we are a development shop so we tend to be exposed to more of the finer issues of SQL from time to time. As far as just doing the steps outlined in the KB article, I'm hoping it will recreate the same certificate but from the looks of it, it might actually create a new certificate. Are you reading that the same? It appears the code to actually create the certificate is in installation SQL scripts. I've thought about just dropping and recreating both of them using that code (it contains the password to use).
Elliott Whitlow
Elliott Whitlow
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10004 Visits: 5314
I got the impression that it recreated it. One thing I've been thinking about it your server master key, you might be able to regen it and get them in sync, but I'm not sure.

I also believe that when those keys are regenerated and the existing box didn't have a valid decryption key there is some loss, such as you can no longer decrypt existing SQL passwords and such.. I am basing this one when I restored a master to a new box I couldn't access any of the linked servers because the credentials could not be decrypted because the server master key didn't match, when I restored that I was back in business.

There are others who probably know more about this particular item than I..

CEWII
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 202
Ok, I've been reviewing the installation scripts and I think I know what needs to be done. Here is my summary of steps:

- Create certificate ##MS_AgentSigningCertificate## in msdb
- Add the signature of the certificate to specific system procedures (too many to list atm)
- Dump the certificate ##MS_AgentSigningCertificate## in msdb to file
- Create certificate ##MS_AgentSigningCertificate## in master from the file
- Create server login ##MS_AgentSigningCertificate## from the certificate in master
- Create database user ##MS_AgentSigningCertificate## for the certificate in master
- Enable the certificate for ODB (not sure what this is yet but it's in the script Smile)
- Grant execute to ##MS_AgentSigningCertificate##

Obviously these steps assume no objects already exist so logic will need to be added to account for these conditions. But with this direction, does anyone see any problems with a script that performs all these actions? Any steps that might be overlooked? I'll post a sample script later.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search