Sql-server – Cleaning up specific domain logins on about 50 servers and therefore the users

This is a much more complicated answer than you might expect. The high-level construct is pretty simple. In this order:

1. drop any database users in each database that are associated with a server-level login from the old abc domain
2. drop all the server-level logins from the old abc domain

It has to be in this order -- either one step of [all the database users from all the databases] and then one step of [all the logins], or repeated steps of [each login's database user] and then [each login]. One way to do this is using dynamic SQL, but the nested single quotes gut pretty ugly pretty fast. To drop the users inside each database:

DECLARE @innersql nvarchar(max) = N'PRINT DB_NAME();
  DECLARE @sql nvarchar(max) = N''''; 
  SELECT @sql += N''
      DROP USER '' + QUOTENAME(name) + '';'' 
    FROM sys.database_principals 
    WHERE name LIKE N''abc\%''; 
  PRINT @sql;
  --EXEC sys.sp_executesql @sql;'; -- happy? uncomment

DECLARE @db sysname, @exec nvarchar(max);

DECLARE c CURSOR FOR SELECT name FROM sys.databases;

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
  EXEC @exec @innersql;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c;
DEALLOCATE c;
GO

Then to drop the logins at the server level:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
DROP LOGIN ' + QUOTENAME(name) + N';' 
  FROM sys.server_principals
  WHERE name LIKE N'NT Service\%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql; -- happy? uncomment

There are complications, of course.

You might find that the above commands fail, for some or all users/logins. Reasons may vary. For example:

• What if a database user was created from a Windows login, but without the domain\login convention? For example:

  USE yourdb;
  GO
  CREATE USER splunge FROM LOGIN [abc\login1];
  

  In this case, because the server-level login doesn't have to exist first, you can't tell whether that login is from domain abc or foo. So, the important question here is:

  Is this database user safe to drop? Maybe you can determine this based on create_date or modify_date in sys.database_principals, some pattern in the aliased user name, or external documentation.

• What if a login owns/controls objects? schemas? jobs? databases? Example:

  USE master;
  GO 
  CREATE LOGIN [abc\login2] FROM WINDOWS;
  CREATE LOGIN [abc\login3] FROM WINDOWS;
  GO
  ALTER AUTHORIZATION ON DATABASE::yourdb TO [abc\login2];
  GO
  EXEC msdb.dbo.sp_update_job @job_name = N'syspolicy_purge_history', 
    @owner_login_name = N'abc\login2';
  
  USE yourdb;
  GO
  CREATE USER [abc\login3] FROM LOGIN [abc\login3];
  GO
  CREATE SCHEMA blat AUTHORIZATION [abc\login3];
  

  All of these things can block the attempt to drop either the database user or the server-level login. I can't drop login2 from the server because they own a database and a job. I can't drop login3 from the database because they own a schema.

  When I try to drop the login abc\login2:

  Msg 15174, Level 16, State 1
  Login 'abc\login2' owns one or more database(s). Change the owner of the database(s) before dropping the login.

  If the login doesn't own a database but owns a job:

  Msg 15170, Level 16, State 1
  This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped.

  If I try to drop the user abc\login3:

  Msg 15138, Level 16, State 1
  The database principal owns a schema in the database, and cannot be dropped.

  So, the important question here is:

  What must I do to drop this login?

  You must first:

  1. Transfer all the database-level things the database-level user controls to a new controller.
  2. Drop the database-level user.
  3. Transfer all the server-level things the server-level login controls to a new controller.

  4. Drop the server-level login.

     .

  All of which isn't hard, for this one account. What if you have 50 or 100 of these, across 50 or 100 databases?

We need to extend our script.

First, before we drop a user from the database, we need to switch any of their schemas to someone else (let's say dbo for simplicity):

DECLARE @innersql nvarchar(max) = N'PRINT DB_NAME();
  DECLARE @sql nvarchar(max) = N''''; 

  SELECT @sql += N''
      ALTER AUTHORIZATION ON SCHEMA::'' + s.name + '' TO sa;''
    FROM sys.schemas AS s
    INNER JOIN sys.database_principals AS dp
    ON s.principal_id = dp.principal_id
    WHERE dp.name LIKE N''abc\%'';

  SELECT @sql += N''
      DROP USER '' + QUOTENAME(name) + '';'' 
    FROM sys.database_principals 
    WHERE name LIKE N''abc\%''; 

  PRINT @sql;
  --EXEC sys.sp_executesql @sql;'; -- happy? uncomment

DECLARE @db sysname, @exec nvarchar(max);

DECLARE c CURSOR FOR SELECT name FROM sys.databases;

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';
  EXEC @exec @innersql;
  FETCH NEXT FROM c INTO @db;
END

CLOSE c;
DEALLOCATE c;
GO

For logins, we need to transfer databases and jobs to a different owner, let's say sa (again, for simplicity):

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
    ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(db.name) + N' TO sa;'
  FROM sys.databases AS db
  INNER JOIN sys.server_principals AS sp
  ON sp.sid = db.owner_sid
  WHERE sp.name LIKE N'NT Service%';

SELECT N'
    EXEC msdb.dbo.sp_update_job @job_name = N''' 
      + REPLACE(jobs.name, '''', '''''') + N''',
      @owner_login_name = N''sa'';'
  FROM msdb.dbo.sysjobs AS jobs
  INNER JOIN sys.server_principals AS sp
  ON sp.sid = jobs.owner_sid
  WHERE sp.name LIKE N'NT Service%';

SELECT @sql += N'
DROP LOGIN ' + QUOTENAME(name) + N';' 
  FROM sys.server_principals
  WHERE name LIKE N'NT Service\%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql; -- happy? uncomment

That is not exhaustive. There are probably other security-related things that can prevent a user or login from being dropped, but those are the main ones I could think of.

And again, if you have the scenario above where you have a user in a database that is mapped to a server-level domain login but the user name does not contain the domain name prefix, you're going to have to add rules to capture them.