Re: Execute permission denied on object xp_SQLagent_notify
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 24 Feb 2007 15:02:20 +0000 (UTC)
Gabriela Nanau (Gabriela Nanau@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
SQL Server 2000, SP4. I have this login MyLogin which has access in both
msdb and master. The account is public in master and db_owner in msdb. I
have the same settings on 10 servers. I attempt to create a job, steps
and schedule. When it comes to msdb.dbo.sp_add_jobserver I got the
following messages:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 7
EXECUTE permission denied on object 'xp_sqlagent_is_starting', database
'master', owner 'dbo'.
Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 175
EXECUTE permission denied on object 'xp_sqlagent_notify', database
'master', owner 'dbo'.
The puzzling part here is that I got the errors on 2 servers out of the
10 above mentioned. On the other 8 the job is created successfully and
there are no explicit rights granted or denied on these particular XPs.
Question: What are the minimum requirements to execute the above 2 XP?
They are not documented by Microsoft or it seems I cannot find much on
them. There is always the possibility to explicitly GRANT access to them
for MyLogin, but the question remains: why is it working on 8 servers
and not working on the other 2. Must be some other setting somewhere!
I would guess this is a owner-chaining issue. Since there are no perms
granted to revoked to these SP:s, MyLogin should not be able to execute
these procedures directly on any server.
However, when MyLogin runs sp_add_jobserver, permission is granted
through ownership chaining, if the two procedures have the same owner.
To have this:
1) The two databases must have the same owner.
2) Cross-DB chaining must be enabled for the databases.
According to Books Online is DB chaining always on for master and msdb,
so my guess is that on the two servers you have problem, master and msdb
have different owners.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: Password is "too recent to change"
- Next by Date: Re: Backup
- Previous by thread: Re: Execute permission denied on object xp_SQLagent_notify
- Next by thread: Re: Execute permission denied on object xp_SQLagent_notify
- Index(es):
Relevant Pages
|
