SQL Server 2000, SP4. I have this login MyLogin which has access in both msd
b
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 ar
e
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. Ther
e
is always the possibility to explicitly GRANT access to them for MyLogin, bu
t
the question remains: why is it working on 8 servers and not working on the
other 2. Must be some other setting somewhere!
Any answer will be higly appreciated.Gabriela,
On my server the xp_sqlagent* stored procedures are granted execute to
public. Check on your two problem servers to see whether that is true for
you.
If the agent XPs are not enabled, you can do so by:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
RLF
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> 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!
> Any answer will be higly appreciated.|||As I said in my first post, I don't have GRANT or DENY for the xp_SQLAGENT%
SPs (not even for public) on any of the servers (the ones that work or the
ones that don't).
As for the sp_configure 'Agent SPs', I am on SQL Server 2000, not such
option there!
So thanks, but it doesn't help.
Gabriela Nanau
MCDBA
"Gabriela Nanau" wrote:
> SQL Server 2000, SP4. I have this login MyLogin which has access in both m
sdb
> and master. The account is public in master and db_owner in msdb. I have t
he
> 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? The
y
> are not documented by Microsoft or it seems I cannot find much on them. Th
ere
> 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 th
e
> other 2. Must be some other setting somewhere!
> Any answer will be higly appreciated.|||Gabriela Nanau (Gabriela Nanau@.discussions.microsoft.com) 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can fix msdb ownership can database options
using the script below.
USE msdb
EXEC sp_changedbowner 'sa'
EXEC sp_dboption 'msdb', 'db chaining', true
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in message
news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
> 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!
> Any answer will be higly appreciated.|||Thanks a lot. The different ownership was indeed, the problem! I didn't even
think to check the owners for these 2 databases! It seemed so obvious that i
t
must be sa! That was a rookie's error, I'm sort of embarrassed! Now that I
think about, the msdb was brought from a different machine at some point and
it was probably then when the owner changed.
Once again thanks!
--
Gabriela Nanau
MCDBA
"Dan Guzman" wrote:
> To add to Erland's response, you can fix msdb ownership can database optio
ns
> using the script below.
> USE msdb
> EXEC sp_changedbowner 'sa'
> EXEC sp_dboption 'msdb', 'db chaining', true
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gabriela Nanau" <Gabriela Nanau@.discussions.microsoft.com> wrote in messa
ge
> news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@.microsoft.com...
>
min: