Table of Contents

Microsoft - Microsoft SQL Server - SQL - Users - Remove Users from Databases

Remove Users from Databases without Exclusion

CREATE PROCEDURE Remove_User_from_ADM_Databases
@name nvarchar (50)
AS
DECLARE @SQL nvarchar(MAX)
SET  @SQL = 'USE [?]; 
    DECLARE @Tsql NVARCHAR(MAX)
    SET @Tsql = ''''
    SELECT @Tsql = ''DROP USER '' + d.name
    FROM sys.database_principals d
    JOIN master.sys.server_principals s
        ON s.sid = d.sid
    WHERE s.name = '''+@name+'''
    EXEC (@Tsql)'
EXECUTE master.sys.sp_MSforeachdb @SQL

Remove Users from Databases with Exclusion

CREATE PROCEDURE Remove_User_from_ADM_Databases1
@name nvarchar (50)
AS
DECLARE @SQL nvarchar(MAX)
SET  @SQL = 'USE [?]; 
IF ''?'' NOT IN (''dswW01_UK_CMM_W01_UK_22_SIT2_20201023'',''dswW01_UK_FIN_W01_UK_22_SIT2_20201023'')
    DECLARE @Tsql NVARCHAR(MAX)
    SET @Tsql = ''''
    SELECT @Tsql = ''DROP USER '' + d.name
    FROM sys.database_principals d
    JOIN master.sys.server_principals s
        ON s.sid = d.sid
    WHERE s.name = '''+@name+'''
    EXEC (@Tsql)'
 
print SUBSTRING (@SQL,1,2000)
print SUBSTRING (@SQL,2001,4000)
print SUBSTRING (@SQL,4001,6000)
--EXECUTE master.sys.sp_MSforeachdb @sql