As a SQL Server administrator, you may often come across situations where you need to drop database users and their associated logins from servers. Manually handling this process can be time-consuming and tedious. In this article, we will discuss a script that automates the process of dropping users and logins, and also addresses the issue that arises when a user owns a schema.
Let’s start by creating a SQL Login named Beevis and giving him db_datareader access 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]
GOIn addition, let’s give Beevis ownership of the Beevis schema:
CREATE SCHEMA Beevis AUTHORIZATION Beevis;Now, let’s use the script to drop Beevis and his associated login:
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;By executing this script, the user/login Beevis will be removed, and the ownership of the Beevis schema will be transferred to the dbo schema.
However, there may be times when you encounter the “Login has been granted one or more permissions…” message. In such cases, you can use the following script to identify the permissions and their grantors:
DECLARE @name sysname = N'';
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; This script will provide you with information about the permissions granted to the specified user/login and their grantors.
By having these scripts on hand, you can easily manage and drop users and logins in SQL Server. In future versions, you can even incorporate handling for permissions issues and automate the script generation process. These scripts will save you time and effort when dealing with user and login management tasks.
Hope you find this information helpful in your SQL Server administration tasks!