db_owner role

I am getting this error message when disabling a job. The user is not a SA.

TITLE: Microsoft.SqlServer.Smo

Alter failed for Job 'XYZ'.

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

EXECUTE permission denied on object 'sp_help_operator', database 'msdb', owner 'dbo'. (Microsoft SQL Server, Error: 229)

The user can diasble the job if i give db_owner permission on msdb.

Is there a way i can do this without making the user db_owner?

Thanks for any help

There is no specific db_role for job administation, but you can create one. Grant the appropiate permission to the role that you need and assign the user to that role. You sure can also only grant the specific user the rights for that, but the next time you want another user to do the job you will have to repeat your work for that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks.

Could you describe the "Grant the appropriate permission to the role that you need" in as little more detail as to what has to be done?

Thanks for the help

|||

I don′t know the complete list of permissions you will need for your work to be done, so you would have to go the iterative way to get your work done, here are the steps to complete:

-Create a db Role in the Database msdb (as you want to administer the alerts and things related to the SQL Agent)
-Assign users to this role that should do the administrative work.
-Assign the appropiate permissions to that db role (where the users are currently in) Put in the execute right for that procedure you get an error for.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de