The script below will create SQL code to drop every user and login from a SQL Server instance. Not very useful unless you’re trying to give someone, possibly yourself, a very bad day. Don’t worry though the script does not execute the code, to reiterate it only generates it. However given the potential for you using the output of the script incorrectly this post comes with a disclaimer (Link to disclaimer).
To make the script a little more useful you can populate a temp table called #TheseUsersOnly in the script with the specific login/user names you want to remove.
This will limit the code outputted to only the logins and users specified.
Go to the /*INSERT LOGINS HERE*/ section to populate the temp table with hard coded login/user names.
Be sure to thoroughly review the code outputted before executing it.
If you encounter this error message “The database principal owns a schema in the database, and cannot be dropped“, see this post (Link).
SET NOCOUNT ON;
/*DECLARE VARIABLES*/
DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @DbName AS SYSNAME;
DECLARE @sqlCommand AS VARCHAR(MAX);
DECLARE @UserName AS VARCHAR(128);
DECLARE @i AS INT;
DECLARE @z AS INT;
DECLARE @j AS INT;
DECLARE @y AS INT;
/*DROP EXISTING TEMP TABLES*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
DROP TABLE #ErrorTable
END;
IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
DROP TABLE #User
END;
IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
DROP TABLE #UserOrdered
END;
IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
DROP TABLE #TheseUsersOnly
END;
IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
DROP TABLE #Return
END;
/*CREATE TEMP TABLES*/
/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];
CREATE TABLE #User (
Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,DbName SYSNAME NULL
,UserName SYSNAME NULL
);
CREATE TABLE #UserOrdered (
Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,DbName SYSNAME NULL
,UserName SYSNAME NULL
);
CREATE TABLE #TheseUsersOnly (UserName SYSNAME NULL);
CREATE TABLE [#Return] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];
/*INSERT LOGINS HERE*/
/*
/*Single Login*/
INSERT INTO #TheseUsersOnly (UserName)
SELECT 'Test_Login';
*/
/*
/*Multiple Logins*/
INSERT INTO #TheseUsersOnly (UserName)
VALUES (Test_Login_1)
,(Test_Login_2)
,(Test_Login_3)
*/
SET @DbName = '';
/*GENERATE LIST TABLE OF DATABASE NAMES*/
INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC;
/*GENERATE LIST OF USERS FOR EACH DATABASE*/
WHILE @DbName IS NOT NULL
BEGIN
SET @DbName = (
SELECT MIN(DbName)
FROM @Database
WHERE DbName > @DbName
)
SET @sqlCommand = '
INSERT INTO #User (
DbName
,UserName
)
SELECT ' + '''' + @DbName + '''' + ' AS DbName
,princ.name AS UserName
FROM ' + QUOTENAME(@DbName) + '.sys.database_principals AS princ
WHERE princ.name IS NOT NULL
';
/*OPTION TO LIMIT USERS TO ONLY THE USERS SPECIFIED*/
IF EXISTS (
SELECT *
FROM #TheseUsersOnly
)
BEGIN
SET @sqlCommand = @sqlCommand + 'AND princ.Name IN (SELECT UserName FROM #TheseUsersOnly);'
END
ELSE
BEGIN
SET @sqlCommand = @sqlCommand + ';'
END
/*ERROR HANDLING*/
BEGIN TRY
EXEC (@sqlCommand)
END TRY
BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH
END;
/*REORDER USER TABLE BY NAME ALPHABETICALLY ASCENDING*/
INSERT INTO #UserOrdered (
DbName
,UserName
)
SELECT DbName
,UserName
FROM #User
ORDER BY UserName ASC
,DbName ASC;
/*SET LOOP VARIABLES*/
SET @y = 0;
SET @i = 1;
SET @z = (
SELECT COUNT(*)
FROM #UserOrdered
);
/*LOOP TO GENERATE SQL CODE*/
WHILE @i <= @z
BEGIN
SELECT @DbName = DbName
,@UserName = UserName
FROM #UserOrdered
WHERE Id = @i
IF @y = 0
SET @j = 1;
BEGIN
SET @y = (
SELECT COUNT(UserName)
FROM #UserOrdered
WHERE UserName = @UserName
)
END
SET @sqlCommand = 'USE [' + @DbName + '];
IF EXISTS (SELECT * FROM ' + QUOTENAME(@DbName) + '.sys.database_principals WHERE name = N' + '''' + @UserName + '''' + ')
BEGIN DROP USER [' + @UserName + '] END;
';
/*ERROR HANDLING*/
BEGIN TRY
INSERT INTO #Return
SELECT (@sqlCommand)
END TRY
BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH
IF @j = @y
BEGIN
IF EXISTS (
SELECT NAME
FROM master.sys.server_principals
WHERE NAME = @UserName
)
BEGIN
INSERT INTO #Return (SqlCommand)
SELECT 'USE [master];
IF EXISTS (SELECT NAME
FROM master.sys.server_principals
WHERE NAME = ' + '''' + @UserName + '''' + '
) BEGIN
DROP LOGIN [' + @UserName + '] END;'
END
SET @y = 0;
END
SET @i = @i + 1;
SET @j = @j + 1;
END
/*RESULTS*/
SELECT SqlCommand
FROM #ErrorTable;
SELECT SqlCommand
FROM #Return;
/*HOUSEKEEPING*/
IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
BEGIN
DROP TABLE #ErrorTable
END;
IF OBJECT_ID(N'tempdb..#User') IS NOT NULL
BEGIN
DROP TABLE #User
END;
IF OBJECT_ID(N'tempdb..#UserOrdered') IS NOT NULL
BEGIN
DROP TABLE #UserOrdered
END;
IF OBJECT_ID(N'tempdb..#TheseUsersOnly') IS NOT NULL
BEGIN
DROP TABLE #TheseUsersOnly
END;
IF OBJECT_ID('tempdb..#Return') IS NOT NULL
BEGIN
DROP TABLE #Return
END;
Like this:
Like Loading...
Tidbytez 