Automatically Drop a Login/User (and Solve the Schema Owner Problem)

From time to time, we all need to clean out our logins and users as people leave the organization. Have you ever gone to write a process to do that, only to have it stop cold because the user owned a schema? Then you have to hunt down what schema is owned and try, try again. So frustrating.

The way I am about to show you will first modify any schema that the user may own and set it to ‘dbo’, then it will drop the user out of any databases it may be in, and finally, it will drop the login.

For an easy demo, I used the AdventureWorks2012 database. I’ll create a login and user, and then set the user to own a schema.

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'abcdefg', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestUser]
GO
CREATE SCHEMA testSchema AUTHORIZATION TestUser

Yes, I know….it’s hardly a secure password. It’s a demo. Please don’t judge me.

Now that we have the basic architecture in place, let’s go ahead and run the script.

IF OBJECT_ID('tempdb..#Users') IS NOT NULL
    DROP TABLE #Users;
IF OBJECT_ID('tempdb..#SchemaOwners') IS NOT NULL
    DROP TABLE #SchemaOwners;
CREATE TABLE #Users
(
    DatabaseName sysname,
    name sysname
);
CREATE TABLE #SchemaOwners
(DatabaseName sysname,
    SchemaName sysname,
    UserName sysname
);
DECLARE @SearchObject NVARCHAR(MAX) = 'TestUser'
DECLARE @SchemaName sysname;
DECLARE @SchemaCursor CURSOR;
DECLARE @SchemaSQL NVARCHAR(MAX);
DECLARE @SchemaSearch NVARCHAR(MAX) = @SearchObject
SET @SchemaCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
ORDER BY name;
OPEN @SchemaCursor;
FETCH NEXT FROM @SchemaCursor
INTO @SchemaName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SchemaSQL
        = N'USE [' + @SchemaName
          + N']; 
INSERT INTO #SchemaOwners
SELECT ''' + @SchemaName + N''',
s.name AS SchemaName,
       u.name AS UserName
FROM sys.schemas s
    INNER JOIN sys.sysusers u
        ON u.uid = s.principal_id
WHERE u.name LIKE ''%' + @SchemaSearch + N'%'';';
    PRINT @SchemaSQL;
    EXECUTE sp_executesql @SchemaSQL;
    FETCH NEXT FROM @SchemaCursor
    INTO @SchemaName;
END;
CLOSE @SchemaCursor;
DEALLOCATE @SchemaCursor;
DECLARE@AlterDBSchemaName SYSNAME
DECLARE @AlterSchemaName SYSNAME
DECLARE@AlterSchemaCursor CURSOR
DECLARE @AlterSchemaSQL NVARCHAR(MAX)
SET @AlterSchemaCursor = CURSOR FAST_FORWARD LOCAL
FOR SELECT DatabaseName,
       SchemaName
FROM #SchemaOwners;
OPEN @AlterSchemaCursor
FETCH NEXT FROM @AlterSchemaCursor INTO @AlterDBSchemaName, @AlterSchemaName
WHILE (@@FETCH_STATUS = 0) 
BEGIN
SET @AlterSchemaSQL = N'USE [' + @AlterDBSchemaName + N'];
ALTER AUTHORIZATION ON SCHEMA:: [' + @AlterSchemaName + N'] TO [dbo];'
PRINT @AlterSchemaSQL
EXECUTE sp_executesql @AlterSchemaSQL
FETCH NEXT FROM @AlterSchemaCursor INTO @AlterDBSchemaName, @AlterSchemaName
END

CLOSE @AlterSchemaCursor;
DEALLOCATE @AlterSchemaCursor;
SELECT DatabaseName,
       SchemaName,
       UserName,
   'Successfully switched to dbo schema'
FROM #SchemaOwners;
DROP TABLE #SchemaOwners;
DECLARE @dbName sysname;
DECLARE @dbCursor CURSOR;
DECLARE @DBSQL NVARCHAR(MAX);
DECLARE @User NVARCHAR(MAX) = @SearchObject
SET @dbCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
ORDER BY name;
OPEN @dbCursor;
FETCH NEXT FROM @dbCursor
INTO @dbName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @DBSQL = N'USE [' + @dbName + N'];
INSERT INTO #Users
SELECT '''        + @dbName + N''', name
FROM sys.database_principals
WHERE name LIKE ''%' + @User + N'%'';';
    PRINT @DBSQL;
    EXECUTE sp_executesql @DBSQL;
    FETCH NEXT FROM @dbCursor
    INTO @dbName;
END;
CLOSE @dbCursor;
DEALLOCATE @dbCursor;
SELECT DatabaseName,
       name,
   'Successfully dropped user'
FROM #Users;
DECLARE @DropName sysname;
DECLARE @name sysname;
DECLARE @DropCursor CURSOR;
DECLARE @DropSQL NVARCHAR(MAX);
DECLARE @Login NVARCHAR(MAX) = @SearchObject
SET @DropCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT DatabaseName,
       name
FROM #Users
ORDER BY DatabaseName;
OPEN @DropCursor;
FETCH NEXT FROM @DropCursor
INTO @DropName,
     @name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @DropSQL = N'USE ' + @DropName + N'; DROP USER [' + @name + N'];';
    PRINT @DropSQL;
    EXECUTE sp_executesql @DropSQL
    FETCH NEXT FROM @DropCursor
    INTO @DropName,
         @name;
END;
CLOSE @DropCursor;
DEALLOCATE @DropCursor;
DROP TABLE #Users;
DECLARE @LoginDropName sysname;
DECLARE @LoginDropCursor CURSOR;
DECLARE @LoginDropSQL NVARCHAR(MAX);
SET @LoginDropCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.syslogins
WHERE name LIKE '%' + @Login + '%'
ORDER BY name;
OPEN @LoginDropCursor;
FETCH NEXT FROM @LoginDropCursor
INTO @LoginDropName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @LoginDropSQL = N'DROP LOGIN [' + @LoginDropName + N'];';
    PRINT @LoginDropSQL;
    EXECUTE sp_executesql @LoginDropSQL;
    FETCH NEXT FROM @LoginDropCursor
    INTO @LoginDropName;
END;
CLOSE @LoginDropCursor;
DEALLOCATE @LoginDropCursor;
IF (SELECT @@TOTAL_ERRORS) = 0
BEGIN
SELECT 'Successfully dropped login, user and switched schema.  Operation complete!' 
END

In my environment, this runs really quickly (usually under 3 seconds max). Yours may vary. Hope it helps!