Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft - Microsoft SQL Server - SQL - Users - Remove Users from Databases ======

===== Remove Users from Databases without Exclusion =====

<code sql>
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
</code>

----

===== Remove Users from Databases with Exclusion =====

<code sql>
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
</code>