May 10, 2017 at 2:19 pm
We had a prior DBA that had his AD account showing up as the owner on a multitude of SQL Agent jobs. We've gotten them all successfully switched over to sa (with no errors happening so far). Where else should we look for his name potentially being entangled in our instances? We obviously want to delete his account from Active Directory (and the SQL Instances), but we don't want to see anything break. So far, the only other things I can think of are:
- MSQL Services
- If his account were used as a service account for an application
Thanks,
--=Chuck
May 10, 2017 at 2:24 pm
If he did that, please do not refer to him as a DBA! It's an insult to those of us that are!
Aside from the two places you thought of, how about checking folder permissions, windows scheduled tasks, and possibly things that are stored in his profile on the servers?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 10, 2017 at 2:38 pm
Windows scheduled tasks ... good idea, thanks.
If the MSSQL services are not running under his login (and, they never were, luckily), what would his folder permissions and items in his profile affect, in the context of SQL Server itself?
--=cf
May 10, 2017 at 2:55 pm
SQL Server service account needs access to the folders where the data and log files reside, as well as the binaries.
His account was probably an admin level account.
If his account was running an app, be it SQL or whatever, and you change the service account, that account may need to have permissions granted to the folder/files.
If he had something scheduled to run, and stored it in his profile, a scheduled task may not have access to this folder.
If he had jobs running as himself that moved, as an example, a backup from the local drive to a netwrk share, you need to make sure all the permissions are in place.
You task is painful. The only way you can tell defintivly is to disable his account, and see what breaks!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 10, 2017 at 5:47 pm
Is the account the owner of any databases?
Is it the owner of any schemas in any databases?
I shudder even asking this one, but does the account own any database roles in any databases?
May 10, 2017 at 6:03 pm
I forgot about that one. I just put up with that fun myself.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 11, 2017 at 11:34 am
Thanks to both of you. Looks like I'm good on role ownership, but I'm seeing database & schema ownership all over the place.
--=cf
May 11, 2017 at 12:18 pm
Here's a handy script you could use which will generate Alter Authorization commands for the sa on the instance for you on all the databases in the instance if they are not owned by SA:
Use Master;
GO
Select [name] AS [DBName],
SUSER_SNAME([owner_sid]) AS [ExistingOwner],
N'ALTER AUTHORIZATION ON DATABASE::'
+ QUOTENAME([name]) + ' TO '
+ QUOTENAME(( SELECT [name]
FROM [sys].[sql_logins]
WHERE [sid] = 0x01
)) AS [ScriptToChangeDBOwner]
FROM [sys].[databases]
WHERE [database_id] > 4
AND [owner_sid] <> 0x01
AND [state_desc] = 'ONLINE';
GO
May 12, 2017 at 3:12 am
chuck.forbes - Wednesday, May 10, 2017 2:19 PMWe had a prior DBA that had his AD account showing up as the owner on a multitude of SQL Agent jobs. We've gotten them all successfully switched over to sa (with no errors happening so far). Where else should we look for his name potentially being entangled in our instances? We obviously want to delete his account from Active Directory (and the SQL Instances), but we don't want to see anything break. So far, the only other things I can think of are:- MSQL Services
- If his account were used as a service account for an applicationThanks,
--=Chuck
If you're using database mirroring or availability groups check the mirroring endpoints, they'll be owned by the person who created them by default.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2017 at 4:13 am
It is advisable to create AD account to run SQL Server services. Which will be easy to manage with better security.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply