What happens if agent job owner account is deactivated in Active directory?Linked Server Security under SQL Agent Job ContextSQL Server - SQL Agent - Allow a Group to own a jobGrant Admin to an Active Directory account in SQL ServerCreate Linked Server using Active Directory accountProper way to setup SQL Agent accountHow to change db owner after the associated AD account is disabledJob not running as agent but as engine accountSQL Server agent job questionSQL Server Agent Server Login AccountSQL Agent Job dependency
Is 95% of what you read in the financial press “either wrong or irrelevant?”
How might a landlocked lake become a complete ecosystem?
How to describe a building set which is like LEGO without using the "LEGO" word?
Why can't I share a one use code with anyone else?
Developers demotivated due to working on same project for more than 2 years
is it correct to say "When it started to rain, I was in the open air."
Automation Engine activity type not retrieving custom facet
Why are BJTs common in output stages of power amplifiers?
Where to find every-day healthy food near Heathrow Airport?
Why did the soldiers of the North disobey Jon?
Is 12 minutes connection in Bristol Temple Meads long enough?
Help understanding this line - usage of くれる
Why is it harder to turn a motor/generator with shorted terminals?
Source of the Wildfire?
Do we have C++20 ranges library in GCC 9?
Substring join or additional table, which is faster?
Generate ladder of integers using the least number of unique characters (in C++)
The meaning of the Middle English word “king”
Promotion comes with unexpected 24/7/365 on-call
Alias for root of a polynomial
Were any toxic metals used in the International Space Station?
Is there any way to adjust the damage type of the Eldritch Blast cantrip so that it does fire damage?
Can my Serbian girlfriend apply for a UK Standard Visitor visa and stay for the whole 6 months?
White foam around tubeless tires
What happens if agent job owner account is deactivated in Active directory?
Linked Server Security under SQL Agent Job ContextSQL Server - SQL Agent - Allow a Group to own a jobGrant Admin to an Active Directory account in SQL ServerCreate Linked Server using Active Directory accountProper way to setup SQL Agent accountHow to change db owner after the associated AD account is disabledJob not running as agent but as engine accountSQL Server agent job questionSQL Server Agent Server Login AccountSQL Agent Job dependency
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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?
sql-server sql-server-agent
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
add a comment |
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?
sql-server sql-server-agent
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
add a comment |
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?
sql-server sql-server-agent
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
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?
sql-server sql-server-agent
sql-server sql-server-agent
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
asked May 3 at 11:10
wilson_smythwilson_smyth
1261
1261
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
answered May 3 at 13:35
ShankyShanky
15k32144
add a comment |
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.
edited May 3 at 11:52
Max Vernon
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237293%2fwhat-happens-if-agent-job-owner-account-is-deactivated-in-active-directory%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered May 3 at 13:35
ShankyShanky
15k32144
add a comment |
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.
answered May 3 at 13:35
ShankyShanky
15k32144
add a comment |
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.
answered May 3 at 13:35
ShankyShanky
15k32144
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.
answered May 3 at 13:35
ShankyShanky
15k32144
answered May 3 at 13:35
ShankyShanky
15k32144
answered May 3 at 13:35
ShankyShanky
15k32144
answered May 3 at 13:35
ShankyShanky
15k32144
15k32144
add a comment |
add a comment |
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.
edited May 3 at 11:52
Max Vernon
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
add a comment |
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.
edited May 3 at 11:52
Max Vernon
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
add a comment |
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.
edited May 3 at 11:52
Max Vernon
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
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.
edited May 3 at 11:52
Max Vernon
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
edited May 3 at 11:52
Max Vernon
53.3k13116234
edited May 3 at 11:52
Max Vernon
53.3k13116234
edited May 3 at 11:52
Max Vernon
53.3k13116234
53.3k13116234
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
answered May 3 at 11:15
Andreas BergdalAndreas Bergdal
91
91
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237293%2fwhat-happens-if-agent-job-owner-account-is-deactivated-in-active-directory%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
