Tuesday, 30 June 2015

How to give 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.

No comments:

Post a Comment