CODEWITHSUNDEEP
sql-serverssisdatabase-administrationconfiguration-management
J Weezy
J Weezy

Reputation: 3935

Service account deleted from SQL Server but SQL Agent Job is still running under the context of the deleted account. How?

We are using SQL Server 2014.

Background: This is a follow-up question to researching configuration management issues across SQL Server environments (i.e., development, test, stage, and production) - see the link to the original question below.

I have found in the stage and production environments that the service account responsible for running SQL Agent Jobs has been deleted (it does not appear in the Security->Logins folder) but the job still runs indicating that it is running under the context of the deleted service account.

Research: I attempted to replicate this in the development environment by deleting the service account; however, I received an error message stating that the service account is the owner of existing jobs. So, I am not able to delete the service account. I have confirmed that the service account is listed as the owner of the jobs in the stage and production environment.

When I open the job in both the stage and production environment, and open the individual steps, the Run As dropdown is blank (though grayed out b/c I lack permissions to change it).

Question: How is this possible?

Update: The service account is listed as the owner of the job.

Integration Services Catalog folder permissions changed

Upvotes: 1

Views: 404

Answers (1)

userfl89
userfl89

Reputation: 4790

To view logins you'll need to have VIEW DEFINITION granted to your login for each login account that you want to view. If you want to view all logins, then ALTER ANY LOGIN will need to be granted. I'm guessing you wouldn't grant these to yourself, but to get an idea of them an example of these commands is below. These commands must been run from the master database as noted.

VIEW DEFINITION: 

USE MASTER;

GRANT VIEW DEFINITION ON LOGIN::LoginToView TO YourLogin

ALTER ANY LOGIN:

USE MASTER;

GRANT ALTER ANY LOGIN to YourLogin

Upvotes: 1

Related Questions