Read, Write and Execute permission to SQL Login

I have a web application. This application connect to MS SQL SERVER 2017 for data manipulation (SELECT, UPDATE, DELETE, INSERT) and execute stored procedures. The application also run SQL Agent jobs. I need to create a user in the database to allows application to connect and execute queries, stored procedures and run SQL Agent jobs.  

Open SSMS (sql server management studio) login through sysaddmin  acount e.g.  sa

Make sure “user1” has connect permission to yourDB.

Execute this query

use yourDB
go

GRANT EXECUTE TO user1
GRANT SELECT TO user1
GRANT INSERT TO user1
GRANT UPDATE TO user1
GRANT DELETE TO user1

and also execute this

GRANT ALTER ON SCHEMA::dbo TO user1

where user1 is your user

If we want to allow this user to run sql agent jobs, we need to add it to “SQLAgentOperatorRole”. This role will allow the user to run any job on the server.

Now to SQL Agent permissions;

USE [msdb]
GO
CREATE USER [user1] FOR LOGIN [user1]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [user1]
GO

Make sure user has these permissions in MSDB database;

This is a good article on setting up jobs and an idea to integrate those jobs in UI.

How do I let someone execute a SQL Agent job they don’t own?

for troubleshooting, assign user to “sysadmin” Server Role. Make sure to revoke this permission afterwards.

Add to favorites