Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 11:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
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>;) 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).
Post #792839
Posted Wednesday, September 30, 2009 6:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
No opinions?
Post #795686
Posted Wednesday, September 30, 2009 7:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:22 AM
Points: 2,728, Visits: 1,119
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
Post #795698
Posted Thursday, October 1, 2009 3:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
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.


  Post Attachments 
AuditPermissions.zip (18 views, 3.06 KB)
Post #796659
Posted Tuesday, October 6, 2009 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
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?
Post #798800
Posted Tuesday, October 6, 2009 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #798823
Posted Tuesday, October 6, 2009 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Also, in reference to that KB article, you might just want to do it anyway..

CEWII
Post #798824
Posted Wednesday, October 7, 2009 7:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
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).
Post #799129
Posted Wednesday, October 7, 2009 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #799180
Posted Wednesday, October 7, 2009 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 146, Visits: 136
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 :))
- 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.
Post #799299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse