Re: Execute permission denied on object xp_SQLagent_notify

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:36F16EC4-7BFF-421B-B397-2BEB931FF1ED@xxxxxxxxxxxxxxxx
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.

.

Relevant Pages

  • Re: Cant Install SP4 for SQL 2000 / Win2k3
    ... This explains why a reinstall did not fix the errors. ... ran 'sp_resetstatus msdb' from query analyzer, ... > The LSN %S_LSN passed to log scan in database '%.*ls' is invalid. ... > To resolve this problem, obtain the latest service pack for Microsoft SQL ...
    (microsoft.public.sqlserver.setup)
  • Re: Execute permission denied on object xp_SQLagent_notify
    ... it was probably then when the owner changed. ... USE msdb ... EXECUTE permission denied on object 'xp_sqlagent_is_starting', database ... The puzzling part here is that I got the errors on 2 servers out of the 10 ...
    (microsoft.public.sqlserver.security)
  • Re: Cant Install SP4 for SQL 2000 / Win2k3
    ... Looks like your msdb database has been marked as suspect. ... > See the SQL Server errorlog for more information." ...
    (microsoft.public.sqlserver.setup)
  • Re: MSDB (Subspect)
    ... I have one more suggestion to this issue, I feel that during the MSDB mdf ... and LDF physical file copy (cold Backup), they would have started the SQL ... Stop & Start the SQL Server ... > Probably because the database was damaged when you took the backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Can I restore msdb from another sqlserver s backup
    ... Tibor Karaszi, SQL Server MVP ... "Karin W" wrote in message ... > We have the same problem with a suspected MSDB. ... > droped because it'a a system database. ...
    (microsoft.public.sqlserver.server)