0

I have a developer who needs to be able to create jobs in SQL Server Agent. They are part of the security group SQLDA and I granted that group SQLAgentUserRole in msdb. They still have view only access. Do I also need to grant them db_datawriter in msdb for them to be able to create jobs?

Running SQLServer 2019. Permissions are managed through Active Directory groups.

enter image description here

1 Answer 1

0

Your Windows group account for the developers, if mapped correctly, can create/modify/delete jobs with database role membership of SQLAgentUserRole. You don't need to add the db_datawriter database role.

To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. link

sample sp_add_job code below using SQL Authentication (applicable to Windows Authentication):

/* map the user correctly*/
USE [msdb]
GO
CREATE USER [dev] FOR LOGIN [dev]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [dev]
GO


USE msdb;
GO
EXECUTE AS LOGIN = 'dev';  
EXEC dbo.sp_add_job @job_name = N'NightlyBackups';
GO  
REVERT;  
GO  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.