Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-12-01 : 17:02:17
|
| I have a developer attempting to configure a new job in SQL Server (2005, latest SP). They have been able to run it successfully on several environments but on a specific environment the are getting this error:The EXECUTE permission was denied on the object ‘xp_sqlagent_is_starting’, database ‘mssqlsystemresource’, schema ‘sys’.Has anyone seen an error like this before? The steps they followed to create the script were:- via SSMS, created the job manually- via SSMS, opted to script the object out as CREATE -> New WindowI have confirmed that the permissions are identical (both server and all databases) between the environments where this works and the environment where this does not. What could be possible issues that could cause this? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 17:14:59
|
| Why don't you explicitly grant execute permission on master.dbo.xp_sqlagent_is_starting for user? |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-12-01 : 17:45:49
|
quote:
Originally posted by sodeep
Why don't you explicitly grant execute permission on master.dbo.xp_sqlagent_is_starting for user?
I could try that, however that will still leave an issue - even though all the permissions are identical, this should not be occurring. Both these servers were rebuilt last July at the same time, following the same steps and are configured 100% identical (at least they should be :)).If I go ahead and just grant the execute permissions, that still leaves a question unanswered and chances are, this may happen again. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-12-03 : 10:43:09
|
| Any other takers? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-12-03 : 10:59:00
|
| I think you will find the permissions are not in fact identical. Check his membership in groups in the msdb database, and much more importantly in the Windows local administrators group. Typically permissions for extended stored procedures is inherited from sysadmin membership. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-03 : 11:37:11
|
| Is SQL Server Agent (in Services) running under the same log on account on the different systems? |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-12-03 : 13:51:28
|
| I ended up dropping the login/user from the server/databases and readding and applying the same permissions and the problem disappeared. |
|
|
|
|
|
|
The EXECUTE permission was denied on the object...