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.
SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.
To grant users permission to create and schedule their own jobs, use this script:
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