Удаление отсутствующих на сервере логинов для базы / Drop login for the database that are absent on the server

drop_login.sql

USE master;
GO

IF OBJECT_ID('tempdb.dbo.#tmp_sqlQuery') IS NOT NULL
	DROP TABLE #tmp_sqlQuery
GO

CREATE TABLE #tmp_sqlQuery ( sqlQuery NVARCHAR(MAX) )

DECLARE @nameDB NVARCHAR(255)

DECLARE cur_database_permission CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
	SELECT d.name
	FROM sys.databases d
	WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN cur_database_permission

FETCH NEXT FROM cur_database_permission INTO @nameDB

WHILE @@FETCH_STATUS = 0 BEGIN

	DECLARE @sqlQuery NVARCHAR(MAX)
	SET @sqlQuery = '
		SELECT ''USE [' + @nameDB + ']; DROP USER IF EXISTS '' + ''['' + sdp.name + '']''
			FROM [' + @nameDB + '].sys.database_principals AS sdp
			LEFT JOIN master.sys.sql_logins AS ssl ON sdp.name COLLATE Cyrillic_General_CI_AS = ssl.name
			WHERE sdp.type = ''S'' AND sdp.default_schema_name = ''dbo'' AND ssl.principal_id IS NULL AND sdp.name != ''dbo'''

	INSERT #tmp_sqlQuery
		EXEC sys.sp_executesql @sqlQuery

	FETCH NEXT FROM cur_database_permission INTO @nameDB

END

CLOSE cur_database_permission
DEALLOCATE cur_database_permission

SELECT sqlQuery FROM #tmp_sqlQuery

DECLARE @stringQuery NVARCHAR(MAX)

DECLARE cur_drop_user CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
	SELECT sqlQuery FROM #tmp_sqlQuery

OPEN cur_drop_user

FETCH NEXT FROM cur_drop_user INTO @stringQuery

WHILE @@FETCH_STATUS = 0 BEGIN

	EXEC sys.sp_executesql @stringQuery

	FETCH NEXT FROM cur_drop_user INTO @stringQuery

END

CLOSE cur_drop_user
DEALLOCATE cur_drop_user

IF OBJECT_ID('tempdb.dbo.#tmp_sqlQuery') IS NOT NULL
	DROP TABLE #tmp_sqlQuery
GO