Departure of a SQL Server Administrator – What to Check? - JOYK Joy of Geek, Geek News, Link all geek

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?

Departure of a SQL Server Administrator – What to Check?