2

Running SQL Server 2008.

So I need to get around to removing an old users account, but I'm worried that just disabling or deleting it right away might cause some things to stop working properly if they were setup under that account and/or rely upon it in any way.

Is there a script I can run that will tell me if there is anything that might break if I get rid of a specific user account?

4

I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, if they are the owner of a job or database, will changing that to a different owner cause any problems? Maybe, maybe not. I can think of a few things that are easy to find, but not really any way to verify they won't break without actually changing it and seeing what breaks.

For initial discovery, and this is by no means exhaustive (just a start for the most common things off the top of my head). Note that I've broken these into chunks for readability, but you'll want to fire them all at once to reuse the variables declared at the beginning.

SET NOCOUNT ON;
GO

USE [master];
GO

DECLARE @name NVARCHAR(128) = N'some login name',
---------- change this ---------^^^^^^^^^^^^^^^
        @sid VARBINARY(85),
        @pid INT,
        @sql NVARCHAR(MAX) = N'';

-- figure out their sid and *server* principal_id
SELECT @sid = [sid], @pid = principal_id 
  FROM sys.server_principals 
  WHERE name = @name;

-- linked server logins

SELECT [linked_servers] = COALESCE(remote_name, 
  N'<<LOCAL>> (' + @@SERVERNAME + ')')
  FROM sys.linked_logins
  WHERE local_principal_id = @pid;

-- databases with authorization rights

SELECT [databases_owned] = name 
  FROM sys.databases WHERE owner_sid = @sid;

-- jobs owned by this login

SELECT [jobs_owned] = name 
  FROM msdb.dbo.sysjobs 
  WHERE owner_sid = @sid;

-- jobs with this login having an execute as step

SELECT [jobs_with_steps_having_login_as_proxy] = j.name
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
  INNER JOIN msdb.dbo.sysproxylogin AS proxy
  ON s.proxy_id = proxy.proxy_id
  WHERE proxy.[sid] = @sid
  GROUP BY j.name;

-- server roles they are member or owner of

SELECT [member_or_owner_of_server_roles] = r.name
  FROM sys.server_role_members AS rm
  INNER JOIN sys.server_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN sys.server_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id = @pid;

-- server permissions that have been granted or revoked

SELECT [server_permission] = [permission_name], state_desc
  FROM sys.server_permissions
  WHERE grantee_principal_id = @pid;

-- roles in database

SELECT @sql = N'';

SELECT @sql += ' UNION ALL 
SELECT [database] = ''' + name + ''', 
[member_or_owner_of_database_roles] = r.name
  FROM ' + QUOTENAME(name) + '.sys.database_role_members AS rm
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id = 
    (SELECT principal_id FROM ' + QUOTENAME(name) 
    + '.sys.database_principals WHERE sid = @sid)'
  FROM sys.databases 
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- permissions in database

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
  [database] = ''' + QUOTENAME(name) + ''', 
  [permission] = state_desc 
     + '' '' + [permission_name],
  [object] = COALESCE(QUOTENAME(s.name) 
    + ''.'' + QUOTENAME(o.name) + '' ('' 
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
    + '')'', p.class_desc)
FROM ' + QUOTENAME(name) + '.sys.database_permissions AS p
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON p.major_id = o.[object_id]
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases 
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- modules in database with EXECUTE AS

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
  [database] = ''' + QUOTENAME(name) + ''', 
  [object_with_execute_as] = QUOTENAME(s.name) 
    + ''.'' + QUOTENAME(o.name) + '' ('' 
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
    + '')''
FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON m.execute_as_principal_id = dp.principal_id
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON m.[object_id] = o.[object_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases 
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- objects in database the login "owns" (ALTER AUTHORIZATION)

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
  [database] = ''' + QUOTENAME(name) + ''', 
  [object_with_auth] = QUOTENAME(s.name) 
    + ''.'' + QUOTENAME(o.name) + '' ('' 
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
    + '')''
FROM ' + QUOTENAME(name) + '.sys.database_principals AS dp
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON o.[principal_id] = dp.[principal_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases 
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- schemas in database the login "owns"

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT 
  [database] = ''' + QUOTENAME(name) + ''', 
  [schemas_owned] = QUOTENAME(s.name)
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.[sid] = @sid'
  FROM sys.databases 
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

I am sure there are others that could be added to the list - I have no problem helping figure out the query if you tell me what those things are.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.