A SQL Server administrator has left. But what does that actually mean? What do we need to check to make sure that things will continue to work after their Active Directory account is disabled?
Some things to check are:
- SQL Database items
- Database ownership
- SQL Scheduled Task ownership
- Server role membership
- Database role membership
- object ownership
- Windows items
- Windows Scheduled Tasks
- Ownership / login ID for Windows Services
- Local Group Membership
- File Share Ownership
A few things to note:
- This collection of script merely identifies issues
- Don’t blindly implement fixes without proper testing and without a rollback – you may run into trouble
- This collection is not complete
SQL Server
Database ownership
Ideally, databases would be owned by some low-level non-sa account which is disabled, and solely exists to avoid these problems. This is not an ideal world, so we need to check for databases owned by our departing administrator:
DECLARE @LeaverPrincipalName sysname = 'YourCorp\Hugh.Splitter';
SELECT name,
state_desc
FROM sys.databases
WHERE owner_sid =
(
SELECT TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY name
);
Transferring database ownership should be relatively straight-forward and risk-free. Except there are certain applications that depend on the database being owned by a sysadmin, or by a particular account (sa), or by their own service account, rather than checking for membership of the db_owner role… So make sure you test your changes. You’ll need to use the ALTER AUTHORIZATION statement, as follows:
ALTER AUTHORIZATION ON DATABASE::<<DBName>> TO <<NewUserName>>;
SQL Scheduled Task Ownership
When a SQL Server agent job runs a T-SQL job step, that step is executed as the owner, rather than as the SQL Agent account or the sa account. From the documentation linked just above:
Transact-SQL job steps use the EXECUTE AS command to set the security context to the owner of the Job.
So, we need to make sure that our leaver’s account isn’t the owner of any SQL Agent jobs:
SELECT name
FROM msdb.dbo.sysjobs
WHERE owner_sid =
(
SELECT TOP (1) sid FROM sys.server_principals WHERE name = @LeaverPrincipalName ORDER BY name
);
Which is all well and good, except there is the possibility for it to be set to use a proxy account, so we’ll need a more complex query:
SELECT JobName = j.name,
js.step_id,
js.step_name,
JobOwner = SUSER_SNAME(owner_sid),
ProxyName = p.name
FROM
msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j
ON js.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.sysproxies AS p
ON js.proxy_id = p.proxy_id
WHERE
SUSER_SNAME(owner_sid) = @LeaverPrincipalName
OR p.name = @LeaverPrincipalName
ORDER BY
j.name,
step_id;
There’s a handy-looking sp for migrating job ownership en masse – sp_manage_jobs_by_login.
Server Role Membership
What server roles did the user have? This might be helpful when replicating the user’s permissions when transferring job roles to another user – except this should all be done through active directory group membership, amirite?
Assuming, though, that you’re working somewhere where granting permissions to individuals is the done thing, then you’ll want to check the results of the following:
SELECT sprole.name AS [SQL Server Role],
spmember.name AS [Member Name]
FROM
sys.server_role_members srm
INNER JOIN sys.server_principals sprole
ON sprole.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals spmember
ON spmember.principal_id = srm.member_principal_id
WHERE spmember.name = @LeaverPrincipalName;
Database Role Membership
Similarly, you may need/want to check for membership of database roles across all databases on your server:
IF OBJECT_ID('tempdb.dbo.#DBRoleMembers', 'U') IS NOT NULL
BEGIN
DROP TABLE #DBRoleMembers;
END;
CREATE TABLE #DBRoleMembers (DB sysname, DBRoleName sysname, MemberName sysname);
SELECT @sql
= N'
USE [?];
INSERT INTO #DBRoleMembers
SELECT DB_NAME(),
dprole.name,
dpmember.name
FROM sys.database_role_members drm
inner join sys.database_principals dprole
ON dprole.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dpmember
ON dpmember.principal_id = drm.member_principal_id
WHERE dpmember.name = ' + QUOTENAME(@LeaverPrincipalName, '''');
DECLARE @sqlouter NVARCHAR(MAX) = N'master.sys.sp_msforeachdb @command1';
DECLARE @sqlparams NVARCHAR(MAX) = N'@command1 nvarchar(max)';
EXEC sp_executesql @sqlouter, @sqlparams, @sql;
SELECT * FROM #DBRoleMembers ORDER BY DB, DBRoleName, MemberName;
Object Ownership
It might be worth checking for ownership of individual objects within a database, as you won’t be able to delete the user from the database until ownership of objects has been transferred to another user.
SELECT @sql
= N'
CREATE TABLE #ownedobjects (DBName sysname, ObjName sysname, ObjType VARCHAR(60));
exec sp_msforeachdb ''INSERT INTO #ownedobjects SELECT ''''?'''', name, type_desc FROM [?].[sys].[objects] WHERE principal_id = (SELECT sid FROM sys.server_principals WHERE name = '''
+ QUOTENAME(@LeaverPrincipalName, '''') + N''')''
select * from #ownedobjects';
--SELECT @sql;
EXEC (@sql);
As with databases, use the ALTER AUTHORIZATION statement to fix this.
Windows
For the Windows-based things, we’re going to drop into PowerShell
Windows Scheduled Tasks
Windows Scheduled tasks will fail when the owner account is disabled / deleted. Or, indeed, the password is changed or expired – but you don’t run jobs as a user account, do you? Oh, you do?
$username = 'YourCorp\Hugh.Splitter'
Write-Host "Checking ownership of Windows Scheduled Tasks"
get-scheduledtask | where-object {$_.Author -eq $username}
Oops. You might want to check those.
Windows Service Login
And, of course, you don’t use user accounts to run windows services, do you? Let’s check.
Write-Host "Checking Login for Windows Services"
get-wmiobject win32_service | where-object {$_.StartName -eq $username }
Local Group Membership
It might also be worth checking to see which groups the user is in on a particular server (we’ll leave the active directory stuff to the AD team)
Write-Host "Checking Local Group Membership"
get-localgroup | foreach-object -process {
if ( $(get-localgroupmember $_ | where-object {$_.name -eq $username} ) -ne $null )
{
write-host "User $username is a member of $_.name group"
}
}
File Share Ownership
What about file shares? Does your leaver own any of those? I’m not sure if it makes a difference, but it’s worth identifying these in case anyone needs to change the permissions later…
write-host "Checking File Share Ownership"
(get-smbshare | where-object{$_.Path -ne $null -and $_.Path -ne ""} | get-acl | where-object {$_.Owner -eq $username}).PSPath | Convert-Path
Just to unpick this one a little – the first filter is to remove items that don’t have a path, such as the IPC$ share; and the Convert-Path is there to transform the PSPath value of, say, Microsoft.PowerShell.Core\FileSystem::D:\Backups to a more normal-looking D:\Backups
Still to do
- I should probably think about file ownership as well – in case anything depends on that.
- And maybe running this all into a single script that can run against all servers / a list of servers
The big question, though – what else needs to be checked?
Finally
Do you have the passwords / know where the passwords are stored and have access to that system?
