The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'

Question

Post reply

The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'

praveen kumar-491230

Old Hand

Points: 363
More actions
January 19, 2009 at 2:15 am

#200130

I have created a job on webserver login with window authentication. bcoz while login with username & password or (SQL authentication), SQL Agents jobs option is not comes.
Now i am login with username & password and executing below coding :
use msdb
exec sp_update_job @job_name='splendid_mail',@enabled=0
SQL server returns following error :
Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1
"The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'."
Please help me how to solve this problem or give permission to database 'msdb', schema 'dbo'.
Regards
Praveen

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

The user which you are using does not have permission to execute this SP.

Which user you are using to execute the SP? and what the role of that user?

Regards,
Nitin

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 2

praveen kumar (1/19/2009)
I have created a job on webserver login with window authentication. bcoz while login with username & password or (SQL authentication), SQL Agents jobs option is not comes.

Give sysadmin server role to your sql server login and login. you will see the SQL Agents jobs

Regards,
Nitin

praveen kumar-491230 Old Hand Points: 363 More actions · Answer 3

Thanks for reply.

Plz tell me how to give sysadmin server role to your sql server login and login.

Regards

Praveen

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 4

In Object explorer,

- Explore Security Node

- Explore Logins

- Right click on your user and click on property

- Goto Server role tab and tic Sysadmin

- click ok and it's done.

Now login with you user.

Regards,
Nitin

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

While I agree that giving the login sysadmin privileges will make your problem disappear, I don’t think that you should do it. Giving the login sysadmin privileges will give this login way to much permission. This login will be able to anything on the server including dropping any database, running commands on the OS level with xp_cmdshell, shutting down the server, etc’. Instead you can define the login as a user in MSDB and grant him execute permissions on the stored procedure. You can look in BOL for more details on CREATE USER statement and GRANT Object Permissions.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

praveen kumar-491230 Old Hand Points: 363 More actions · Answer 6

Dear nitinpatel31,

problem solved.

Thanks.

Regards

Praveen

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 7

You are welcome Praveen,

This is the very easy to do. But it may case security issue, if you don't want your user to have access of all object of database(s).

As Adi has mentioned earlier, for better security practice you should not give sysadmin to each user who want to execute perticular SP. But should give EXECUTE permission on that SP.