2

There is a specific need to clean up logins from the SQL Servers after migration from one domain say abc to new domain foo.

After migration as we still have those old logins and users under naming of abc\login1, abc\login2... and so on.

How can I perform this clean up to delete all the logins from the server, which has Windows logins say with domain abc\.. and within same script to delete all the related users within the instance for all databases.

I've searched the link SQL Server script to delete accounts no longer in Active Directory but it does not help in deleting users.

Note: These are just Windows logins and not part of any AD group. Just want to drop logins and the db users associated with it. Other domain AD users have been taken care by our AD and wintel team and users are all set. Just doing the clean up here.

  • So you just want to drop all the logins and their database users? You'll deal with adding [back] the AD users from the new domain separately? (Consider that it might be wise to keep these separate so you don't inadvertently (a) add users who have left since the migration or (b) miss users who have joined since the migration.) – Aaron Bertrand Sep 4 '18 at 13:54
  • @AaronBertrand: Yes that's correct. Just want to drop logins and the db users associated with it. Other domain AD users have been taken care by our AD and wintel team and users are all set. Just doing the clean up here. – BeginnerDBA Sep 4 '18 at 14:01
2

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.

  • 1
    Nothing is ever easy. Nice job. – Max Vernon Sep 4 '18 at 18:15
  • Thanks so much Aaron for taking out the time and explaining it briefly. Very well explained and feel great when experts like you [Huge fan from sqlblogs to sentry blogs], revert. Just one question for the eg: "What if a database user was created from a Windows login, but without the domain\login convention? --> How can we find such users within database across all db's in an instance? – BeginnerDBA Sep 6 '18 at 22:31
3

I have had a similar task recently. My solution:

  1. Create a temporary stored procedure #sp_helplogins which is a copy of sp_helplogins but returns only the second rowset.

  2. Get logins to delete (in my case by using sp_validatelogins)

    DECLARE @Login  sysname, @SQL   NVARCHAR(4000)
    
    CREATE TABLE #BadLogins
    (
        [SID]       varbinary(85)
        ,NTlogin    sysname
    )
    
    CREATE TABLE #BadUsers
    (
        LoginName       VARCHAR(100)
        ,DBName         VARCHAR(100)
        ,UserName       VARCHAR(100)
        ,UserOrAlias    VARCHAR(100)
        ,IsDeleted      INT
    )
    
    INSERT #BadLogins
    EXEC sp_validatelogins
    
  3. Get associated users

    DECLARE myCursor CURSOR READ_ONLY
    FOR
    SELECT DISTINCT NTlogin
    FROM #BadLogins
    
    OPEN myCursor
    
    FETCH NEXT FROM myCursor INTO @Login
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
    INSERT #BadUsers
    EXEC #sp_helplogins @Login
    
    FETCH NEXT FROM myCursor INTO @Login
    
    END
    
    CLOSE myCursor
    DEALLOCATE myCursor
    
  4. Drop the users

    DELETE #BadUsers
    WHERE DATABASEPROPERTYEX(DBName, 'Updateability') != 'READ_WRITE'
    
    WHILE EXISTS ( SELECT 1 FROM #BadUsers WHERE IsDeleted = 0 AND UserOrAlias = 'User' ) BEGIN
    
    SELECT TOP 1 @SQL = 'USE ' + DBName + '
    DROP USER ' + QUOTENAME(UserName)
    FROM #BadUsers
    WHERE IsDeleted = 0
        AND UserOrAlias = 'User'
    ORDER BY UserName,DBName
    
    EXEC sp_executesql @SQL
    --SELECT @SQL
    
    UPDATE u
    SET IsDeleted = 1
    FROM #BadUsers u
        JOIN (
            SELECT TOP 1 DBName, UserName
            FROM #BadUsers
            WHERE IsDeleted = 0
                AND UserOrAlias = 'User'
            ORDER BY UserName,DBName
        ) d ON d.DBName = u.DBName AND d.UserName = u.UserName
    WHERE IsDeleted = 0
    
    END
    

Notice that I don't handle a case when a user have an owned schema so the users won't be deleted by my script.

0

I recently did this for 1 domain user, across multiple servers under CMS I don't care about failure so just wanted quick & easy

You have to adjust for all DOMAIN\% user I guess

EXEC sp_msforeachdb 'IF EXISTS (select 1 FROM ?.sys.database_principals (NOLOCK) WHERE name LIKE ''DOMAIN\Username'') 
BEGIN 
USE ?  
--drop schema [DOMAIN\Username]
--drop user [DOMAIN\Username]
--drop login [DOMAIN\Username]
PRINT ''? user dropped''
END
'

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.