Need to grant SQL Agent Service Account rights to execute SQL jobs

Question

I am wondering what kind of permissions [NT SERVICE\SQLSERVERAGENT] accounts needs to run the job?

Below are the steps that have been impleemented yet the job doesn't run with correct results expected.

a. Assign SQLAgentOperatorRole to [NT SERVICE\SQLSERVERAGENT]

b. Execute rights of SP assigned to [NT SERVICE\SQLSERVERAGENT]

Below are the conditions for the SQL Job that executes Stored Proc

• Owner as [NT SERVICE\SQLSERVERAGENT] and Run-as user is NT SERVICE\SQLSERVERAGENT then job fails

• Owner as [NT SERVICE\SQLSERVERAGENT] and Run-as user is blank then job fails

• Owner as [NT SERVICE\SQLSERVERAGENT] and Run-as user is my login id then job runs perfectly.

• Owner as blank/sa/any other user and Run-as user is NT SERVICE\SQLSERVERAGENT then job fails

The job does NOT run any error but instead the job runs SP that executes code but inserts lesser records like 150 rows instead of 9000 rows. I am not sure what else can be done. My goal is that the [NT SERVICE\SQLSERVERAGENT] should be able to execute the SP inside SQL Job and my login (as mentioned in Point #3) should be removed.

Additionally I have created new user in msdb & in my database. Code snippets are below

--Step 1: Creating LOGIN in msdb
USE [msdb]     
GO
CREATE LOGIN [SQLServerAgentUser]
    WITH password = N'Version1!'
GO

--Step 2: Creating user in msdb
USE [msdb]
GO
CREATE USER  [SQLServerAgentUser] FOR LOGIN [SQLServerAgentUser]

----Step 3: Role assignment
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [SQLServerAgentUser]
GO

----Step 4: Creating user in MYDB

USE [Mydb]
GO
CREATE USER [SQLServerAgentUser] FOR LOGIN [SQLServerAgentUser]

----Step 5: Granting execute rights on Stored Proc
USE [Mydb]
GRANT Execute ON [Dim].[USP_PopulateDimProjectOutlook] TO [SQLServerAgentUser]

GRANT EXECUTE ON [Dim].[USP_DL_PopulateCommissionLiveData] TO [SQLServerAgentUser]

None of the steps have worked so far!

My goal is to run jobs not by employee but by SQL Server Agent / Service Account.

P.S.: I am NOT running SSIS package via SQL Job. I am running EXEC stored procedure.

Let me know what am I doing wrong

Answer 1

My goal is to run jobs not by employee but by SQL Server Agent / Service Account.

All you have to do is create the job as a sysadmin, because

"Members of the sysadmin role have permission to create job steps that do not specify a proxy, but instead run as the SQL Server Agent service account, which is the account that is used to start SQL Server Agent."

Implement SQL Server Agent Security

There is no need to grant any additional rights to [NT SERVICE\SQLSERVERAGENT]; it's already a sysadmin.