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


Error 229: Execute permition denied on object.............


Error 229: Execute permition denied on object.............

Author
Message
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 977
Hello again,

People are reporting the following error:
Error 229: Execute permission denied on object 'xp_sqlagent_enum_jobs', database 'master', owner 'dbo'.
Can you tell me what should i check?

This was working until yesterday but now i don't know if anyone of System Administration Team change any privilege of one user.
Teams work separately and this sheet always happens.

Thanks and regards,
JMSM Wink
Adam Bean
Adam Bean
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 2192
What is it that the users are attempting to do?

By default (someone correct me If I'm wrong, as there is no documentation on this xproc), only sysadmins, msdb dbo's (?), and members of the defined msdb SQLAgentRoles (SQLAgentOperatorRole/SQLAgentUserRole/SQLAgentReaderRole) have the ability to use that procedure.

xp_sqlagent_enum_jobs is basically a nice, quick little way to view running jobs.

---
SQLSlayer
Making SQL do what we want it to do.
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)Mr or Mrs. 500 (516 reputation)

Group: General Forum Members
Points: 516 Visits: 977
Thanks everybody, the solution was as follows:

Regards,
JMSM Wink

use master
go
grant select on master.dbo.sysperfinfo to {username}
go
grant execute on master.dbo.xp_sqlagent_notify to {username}
go
grant execute on master.dbo.xp_sqlagent_enum_jobs to {username}
go
grant execute on master.dbo.xp_sqlagent_param to {username}
go
grant execute on master.dbo.xp_sqlagent_is_starting to {username}
go
grant execute on master.dbo.xp_instance_regenumvalues to {username}
go
use msdb
go
grant execute on msdb.dbo.sp_help_alert to {username}
go
grant execute on msdb.dbo.sp_help_notification to {username}
go
grant select on msdb.dbo.sysalerts to {username}
go
grant select on msdb.dbo.sysoperators to {username}
go
grant select on msdb.dbo.sysnotifications to {username}
go
Harold Buckner
Harold Buckner
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 420
I'm having the same permission problems. I use to put my agent operators in the sqlAgentOperatorRole in the msdb database. Thisa would allow them to go under the jobs and view and execute all jobs.

Does anyone know if a SP2 roll up changed this? I did restore msdb from a backup but I do not think it would have changed the XP_ permissions.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4078 Visits: 5843
"I did restore msdb from a backup" - this could be your problem.
Check something has permissions to run xp_sql_agent_is_starting, and that the msdb & master databases have the same owner.
ddg8885
ddg8885
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 148
I searched the error for a while and saw a lot of things about certificates differing between msdb and master. But I checked the certificates and they were identical.

I compared my server to others, and noticed that the ##MS_AgentSigningCertificate## user was missing from the master database. Running the following query worked for me:

USE MASTER
CREATE USER [##MS_AgentSigningCertificate##] FOR LOGIN [##MS_AgentSigningCertificate##]
GO

GRANT EXECUTE TO [##MS_AgentSigningCertificate##]
go
jarglebar
jarglebar
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 240
I also encountered this issue and found the steps at http://support.microsoft.com/kb/2000274 solved my issues. Wanted to add that in case it helps anyone else out.
a4ather
a4ather
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 11
hi dear,

this is the error list what is get after applying this query.
Sunny1
Sunny1
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 745
I restored msdb from other server for hardware migration. The SQLAgent Service account was not in Systemadmin server role and caused the SQL Server Agent service couldn't start due to lack of permission to access master database. I added the SQLServerAgentService account to SysAdmin server role.

SQLServer Agent Service was able to start right away. Just thought about to share.
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