====== 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