Wednesday 6 February 2013

Grant permissions to users to create jobs in Sql Server

There are roles in msdb database that help database administrators to have better granular control over job creation, execution, and browsing:
SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.
To grant users permission to create and schedule their own jobs, use this script:
USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO
USE [msdb]
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'UserName'
GO

To grant a domain group permission to create and schedule their own jobs, use this script:
USE [msdb]
exec sp_addrolemember 'SQLAgentUserRole', 'DomainName\GroupName'

Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.
You can add user to the SQLAgentUserRole in Management Studio (Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties):


No comments:

Post a Comment