Grant user permissions to create jobs in Sql Server 2005
Security model for Sql Server Agent 2005 has been revamped. DBAs will have better granular control over job creation, execution, and browsing. There are newly created roles in msdb for those purposes: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. For more information, go to this url in Book On Line:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/719ce56b-d6b2-414a-88a8-f43b725ebc79.htm
A lot of times, you may want to grant existing users permission to create and schedule their own jobs. Below is the script to accomplish that. Replace MyUser user and login with your own and it will work. In my experience, Management Studio for Sql Server Express cannot see Sql Server Agent in its UI, therefore will not be able to create jobs, even if the login has been granted that right. So you do need the Management Studio for the full version of Sql Server.
USE [msdb]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
GO
USE [msdb]
GO
EXEC sp_addrolemember ‘SQLAgentUserRole’, ‘MyUser’
GO
Andre Martins Said,
December 29, 2008 @
Ji,
Great tip! Very, very helpfull, THX!!!!
Best Regards!
Haidong Ji Said,
December 29, 2008 @
You are welcome Andre.
I have come across situations when I was looking for solutions to a problem, and Google led me to my own site, and I had no idea I encountered the situation before! When I start reading my notes, then the memory would come back. So I am glad that the information here serving as my own knowledge repository also helps others.
John Wagner Said,
April 20, 2009 @
You might want to point to a group, instead of a user like this:
USE [msdb]
exec sp_addrolemember ‘SQLAgentUserRole’, ‘Domain\Group’
That way, you only have to do this once for each SQL server.
Haidong Ji Said,
April 20, 2009 @
Great point, John. Using domain groups is a great way to control access to domain users.