5

We have a number of jobs owned by a DBA. The DBA is now leaving the company so their account will be deactivated in Active Directory.

What affect will this have on the agent jobs?

2

Let us say we are talking about Windows login which part of AD and was owner of a job. Below is more detailed answer with couple of scenarios.

1. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is member of sysadmin fixed server role

In above scenario the job will "still run". The reason is since job was added as administrator in SQL Server there is entry of this owner with SID in sys.server_principal which SQL Server knows, and since the login was member of sysadmin role no "impersonization" will be done and hence job will succeed.

2. The login was removed from AD and from SQL Server.

In this scenario the job will fail which is pretty much obvious

3. The login was removed from AD but not from SQL Server( someone forgot to remove it) and the login is NOT member of sysadmin fixed server role.

In this case job will fail because since owner is not sysadmin impersonization will be done and SQL Server will look for this account in Windows AD, since the account was removed the job will fail.

For SQL Server admin login like SA, even if SA is disabled and is owner of some job when you run the job it would succeed.

Tibor Karaszi has more detailed explanation on this.

0

Seems like they will stop working: https://www.brentozar.com/blitz/jobs-owned-by-user-accounts/

From that page:

If the SQL Server was managed by someone who’s no longer with the company, all their jobs will suddenly stop working when SQL Server can’t verify the job owner’s account. (This can also pop up if there’s an Active Directory problem when the job needs to run.) The fix is to have the built-in SA account own all the jobs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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