Departure of a SQL Server Administrator – What to Check?

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?

This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

1 Response to Departure of a SQL Server Administrator – What to Check?

  1. Pingback: Checks After a DBA Leaves – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.