Drop Login and Users in MS SQL Server

This code will drop the login and users for all databases for a provided user name. and reports on its progress. This only works where the user and login match. This script does not check if users own any objects.

/*********************************************
DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER
Possible errors could be if a user is the owner of a database object.
*********************************************/
 
       SET NOCOUNT ON
       -- USE master
 
       --DECLARE VARIABLES
       DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user
       DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))
       DECLARE @Database_Name NVARCHAR(MAX)
       DECLARE @SQL_Drop_Login NVARCHAR(MAX)   
       DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)
      
 
       PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
             
       --DROP LOGIN FROM SERVER
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'
       IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)
       BEGIN TRY
              SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'
              EXEC (@SQL_Drop_Login)
              PRINT 'Server Principal Dropped'
       END TRY
       BEGIN CATCH
              PRINT 'Server Principal Found But Could Not Drop'
       END CATCH
       ELSE
              PRINT 'Server Principal Not Found'
      
 
       ---- USER DROP PROCEDURE
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'
      
       --LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP
       INSERT INTO @tbl_Databases (Name)
       SELECT Name
       FROM master.sys.databases WITH(NOLOCK)
      
       --LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST
       WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)
       BEGIN
              SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)
              SET @SQL_Drop_Login ='-- USE '+@Database_Name
              +' IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')
              BEGIN TRY
                     DROP -- USER [' + @Dropping_Windows_Logon_Name +']
                     PRINT '' Dropped User in '+@Database_Name+'''
              END TRY
              BEGIN CATCH
                     PRINT '' User Found But Could Not Drop in '+@Database_Name+'''
              END CATCH
              ELSE PRINT '' User Not Found in '+@Database_Name+'''
              '
              EXEC (@SQL_Drop_Login)
              DELETE FROM @tbl_Databases WHERE Name = @Database_Name
       END
 
       PRINT 'End User Drop Procedure'
 
       --END
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
       SET NOCOUNT OFF

/*********************************************

DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER

Possible errors could be if a user is the owner of a database object.

*********************************************/

SET NOCOUNT ON

-- USE master

--DECLARE VARIABLES

DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user

DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))

DECLARE @Database_Name NVARCHAR(MAX)

DECLARE @SQL_Drop_Login NVARCHAR(MAX)

DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)

PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

--DROP LOGIN FROM SERVER

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)

BEGIN TRY

SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'

EXEC (@SQL_Drop_Login)

PRINT 'Server Principal Dropped'

END TRY

BEGIN CATCH

PRINT 'Server Principal Found But Could Not Drop'

END CATCH

ELSE

PRINT 'Server Principal Not Found'

---- USER DROP PROCEDURE

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'

--LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP

INSERT INTO @tbl_Databases (Name)

SELECT Name

FROM master.sys.databases WITH(NOLOCK)

--LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST

WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)

BEGIN

SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)

SET @SQL_Drop_Login ='-- USE '+@Database_Name

+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')

BEGIN TRY

DROP -- USER [' + @Dropping_Windows_Logon_Name +']

PRINT '' Dropped User in '+@Database_Name+'''

END TRY

BEGIN CATCH

PRINT '' User Found But Could Not Drop in '+@Database_Name+'''

END CATCH

ELSE PRINT '' User Not Found in '+@Database_Name+'''

EXEC (@SQL_Drop_Login)

DELETE FROM @tbl_Databases WHERE Name = @Database_Name

END

PRINT 'End User Drop Procedure'

--END

PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

SET NOCOUNT OFF

One thought on “Drop Login and Users in MS SQL Server”

Brandon Carlson says:

2016-03-09 at 22:30

Pretty soon this can be installed on Linux as well. Literally jumped up screaming when I saw this.

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Jon Culp [.com]

One thought on “Drop Login and Users in MS SQL Server”

Leave a Reply Cancel reply

An Information Archive for a Frivolous Human