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.

One comment

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