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. 😜

Here is our user….
…..and here is the schema. Open the login, and ….oh, I’ll stop there (with profound apologies to the writer of “Here is the Church, Here is the Steeple”)

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


Output of the script.
Poof! The user is gone….
….and our schema owner is back to dbo.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s