SQL Agent Job Creation: SQLAgentAdmin
We are using SQL Accounts to access our JUNE CTP Standard build of SQL2005.
(We are cross domains that don't trust each other, and will never will as we are migrating to ActiveX).
We setup a new account: dbo_<dbname> gave it DBO access to the database that it is running on.
We need to create some jobs (that are scripted from the original server) to run in SQL Agent.
Per the documentation: for sp_add_job
We granted this account: dbo_<dbname> the database role of:SQLAgentUserRoleinside themsdb database.
When running the script we get the following error message:
Msg 229, Level 14, State 5, Line 13
SELECT permission denied on object 'sysjobs', database 'msdb', schema 'dbo'.
Msg 14515, Level 16, State 1, Procedure sp_add_job, Line 54
Only a member of the sysadmin or SQLAgentAdmin server roles can add a job for a different owner with @owner_login_name.From SQL Server Management, we can't find a SERVER role or even a DATABASE role for SQLAgentAdmin. So for now, we had to use a SYSADMIN account to generate the jobs, but I would rather not do that, as our developers can/should be able to create their own SQL Agent jobs.Thanks
Yes we are, however... My BOL say something different.
I am using the June CTP of SQL 2005:
The direct link is:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/6ca8fe2c-7b1c-4b59-b4c7-e3b7485df274.htm
"To run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted the SQLAgentUserRole database role in the msdb database."
But then the error message I get is what I posted earlier.
So something is out of allignment here. Either the BOL is not updated to reflect what the database rule truely is. And if the Database message is correct, then where is this: SQLAgentAdmin Server role that the database error message wants...
Then it looks like BOL was updated. I couldn't verify that as I don't have access to older builds of BOL. But here is what a recent BOL says under the permissions section for sp_add_job:
To run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted one of the following SQL Server Agent fixed database roles, which reside in the msdb database:
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
For information about the specific permissions that are associated with each of these fixed database roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
Thanks
Laurentiu
