SET NOCOUNT ON
go
DECLARE @PrintSQL BIT = 1,
@ExecSQL BIT = 1,
@SQL1 VARCHAR(MAX),
@SQL2 VARCHAR(MAX),
@SQL3 VARCHAR(MAX),
@SQL VARCHAR(MAX)
IF OBJECT_ID('TEMPDB..#main') IS NOT NULL
DROP TABLE #main
SELECT b.c1 AS DOMAIN,
b.C2 AS GroupName,
l.name AS FullName,
ISNULL(DB_NAME(DB_ID(l.dbname)), 'master') AS dbname
INTO #main
FROM sys.syslogins l (NOLOCK)
CROSS APPLY Util.dbo.ParseDelimitedColumns4(name, '\') b
WHERE isntgroup = 1
AND name LIKE '%\%'
AND name NOT LIKE 'firstam%'
AND name NOT LIKE 'nt %'
AND NAME NOT LIKE 'BUILT%'
SELECT @SQL1 = Util.dbo.StringConcat('IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + fullname + ''')
DROP LOGIN [' + fullname + ']
', '')
FROM #main
SELECT @SQL2 = Util.dbo.StringConcat('CREATE LOGIN [' + fULLNAME + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + DBNAME + '], DEFAULT_LANGUAGE=[us_english]
', '')
FROM #main
SELECT @SQL3 = Util.dbo.StringConcat('IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + b.fullname + ''')
DROP LOGIN [' + b.fullname + ']
', '')
FROM #main a
INNER JOIN #main b ON a.groupname = b.groupname
AND a.DOMAIN <> b.DOMAIN
WHERE a.DOMAIN = 'isc'
SET @SQL = ISNULL(@SQL1, '') + ISNULL(@SQL2, '') + ISNULL(@SQL3, '')
IF @printsql = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecSQL = 1
AND @SQL <> ''
EXEC(@SQL )