Dropping Database Users and Logins: How it Can Go So Very Wrong, and How You Can Easily Fix It

I used to be asked on a semi-regular basis to drop database

users and their associated logins from servers. 

When I got tired of sneaker-netting the process, I adapted a script that

I found online (if it’s yours, please send me the link so I can give you

credit!) and added automatic handling for the issue that sometimes arises when

a user owns a schema.

Let’s create a SQL Login named Beevis, and give him db_datareader to the AdventureWorks2012 database. 

USE [master]
GO
CREATE LOGIN [Beevis] WITH PASSWORD=N'################', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012]
GO
CREATE USER [Beevis] FOR LOGIN [Beevis]
GO
USE [AdventureWorks2012]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Beevis]
GO

Additionally, let’s give him ownership of the Beevis schema.

CREATE SCHEMA Beevis AUTHORIZATION Beevis;

Now, let’s use our automagical script blow him away.  Incidentally, this also works for AD logins.

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 @SchemaName sysname;
DECLARE @SchemaCursor CURSOR;
DECLARE @SchemaSQL NVARCHAR(MAX);
DECLARE @Name sysname = N'Beevis'
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 ''' + @Name + 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 *
FROM #SchemaOwners;
DROP TABLE #SchemaOwners;
DECLARE @dbName sysname;
DECLARE @dbCursor CURSOR;
DECLARE @SQL NVARCHAR(MAX);
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 @SQL = N'USE [' + @dbName + N'];
INSERT INTO #Users
SELECT ''['        + @dbName + N']'', name
FROM sys.database_principals
WHERE name LIKE ''' + @Name + N'%'';';
    PRINT @SQL;
    EXECUTE sp_executesql @SQL;
    FETCH NEXT FROM @dbCursor
    INTO @dbName;
END;
CLOSE @dbCursor;
DEALLOCATE @dbCursor;
SELECT *
FROM #Users;
SELECT DatabaseName,
       name
FROM #Users
ORDER BY DatabaseName;
DECLARE @DropName sysname;
DECLARE @FinalName sysname;
DECLARE @DropCursor CURSOR;
DECLARE @DropSQL NVARCHAR(MAX);
SET @DropCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT DatabaseName,
       name
FROM #Users
ORDER BY DatabaseName;
OPEN @DropCursor;
FETCH NEXT FROM @DropCursor
INTO @DropName,
     @FinalName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @DropSQL = N'USE ' + @DropName + N'; DROP USER [' + @FinalName + N'];';
    PRINT @DropSQL;
    EXECUTE sp_executesql @DropSQL
    FETCH NEXT FROM @DropCursor
    INTO @DropName,
         @FinalName;
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 @Name + N'%'
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;

Let’s look at the output:

It works.  Not only is the user/login gone, but the schema is fixed. 

However, there are times you get the “Login has been granted one or more permissions….” message.

That is where Plan B comes into play.  Pinal Dave wrote a script addressing this problem, and I added a couple of bells and whistles to it, because my eyes tend to cross when permissions issues start getting complex.

DECLARE @name sysname = N'<insert name here>';
SELECT class_desc,
       *
FROM sys.server_permissions
WHERE grantor_principal_id =
(
    SELECT principal_id FROM sys.server_principals WHERE name = @name
);
SELECT name,
       type_desc
FROM sys.server_principals
WHERE principal_id IN
      (
          SELECT grantee_principal_id
          FROM sys.server_permissions
          WHERE grantor_principal_id =
          (
              SELECT principal_id FROM sys.server_principals WHERE name = @name
          )
      );
SELECT OBJECT_NAME(major_id),
       *
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(@name);
SELECT OBJECT_NAME(major_id),
       p.*
FROM sys.database_permissions p
WHERE grantor_principal_id = USER_ID(@name);
DECLARE @GranteePrincipalID INT =
        (
            SELECT grantee_principal_id
            FROM sys.server_permissions
            WHERE grantor_principal_id =
            (
                SELECT principal_id FROM sys.server_principals WHERE name = @name
            )
        );
--This makes it easier to see who granted what to whom. Unfortunately, not why.
SELECT *
FROM sys.server_principals
WHERE name = @name
      OR principal_id = @GranteePrincipalID;

Here is what that output looks like:

I had to obscure a lot, but the bottom query results correlate to the top results.  The first line of the bottom query results show the grantor of the permissions, and the bottom line is the grantee.  In this case, a login was explicitly denied impersonation on a server role.  I’m using this example because it is really quirky to fix.  Most often, you’ll just reverse the permissions, using pretty standard syntax. Even easier, right click on the login, go to the “Securables” tab, and remove the permissions.  However, if you are a fan of the TSQL approach, this one is not so straightforward, so it’s a good one to show.  It looks like this:

Having this info on hand in one place has made dropping users and

logins much easier to handle when the

need arises.  In a future version, I

could incorporate the permissions issue handling into the first script, and

maybe even construct some conditional dynamic SQL to automatically create the

scripts to deal with the permissions issues. 

I’m not there yet, though.  Hope

this helps you guys in the meantime.