DECLARE @login_name VARCHAR(256) = NULL,
@database_name VARCHAR(256) = NULL,
@set_master_as_default INT = NULL
SET NOCOUNT ON
SELECT [sid]
INTO #sids
FROM sys.server_principals
WHERE 1 = 2
SELECT @login_name = ISNULL(@login_name, '')
SELECT @database_name = ISNULL(@database_name, '')
IF ISNULL(@login_name, '') <> ''
INSERT #sids
SELECT [sid]
FROM sys.server_principals
WHERE name = @login_name
AND name <> 'sa'
IF ISNULL(@database_name, '') <> ''
BEGIN
DECLARE @dbcmd VARCHAR(1000)
SET @dbcmd = 'select p.sid FROM sys.server_principals p join ' + QUOTENAME(@database_name)
+ '.sys.database_principals d on p.sid = d.sid WHERE p.name <> ''sa'''
INSERT #sids
EXEC (@dbcmd)
END
IF ISNULL(@login_name, '') = ''
AND ISNULL(@database_name, '') = ''
INSERT #sids
SELECT [sid]
FROM sys.server_principals
WHERE is_disabled = 0
AND name <> 'sa'
SELECT @set_master_as_default = ISNULL(@set_master_as_default, 0)
SELECT '--Executed by login: ' + CAST(SUSER_NAME() AS VARCHAR(255)) AS [/* Script Info */]
UNION ALL
SELECT '--Script Generated on: ' + CAST(GETDATE() AS VARCHAR(255))
UNION ALL
SELECT '--Specified Login: ' + CAST(@login_name AS VARCHAR(255))
UNION ALL
SELECT '--Specified Database: ' + CAST(@database_name AS VARCHAR(255))
DECLARE @binvalue VARBINARY(256)
DECLARE @pwdhash VARCHAR(514)
DECLARE @sidhash VARCHAR(514)
DECLARE @login VARCHAR(256)
DECLARE @defdb VARCHAR(256)
DECLARE @chkpol VARCHAR(3)
DECLARE @chkexp VARCHAR(3)
SELECT CAST(NULL AS VARCHAR(MAX)) AS [/* Script Enabled Logins */]
INTO #tmp
DECLARE acursor CURSOR
FOR
SELECT name,
CASE @set_master_as_default
WHEN 0 THEN default_database_name
ELSE 'master'
END AS default_database_name,
CASE is_policy_checked
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE ''
END,
CASE is_expiration_checked
WHEN 1 THEN 'ON'
WHEN 0 THEN 'OFF'
ELSE ''
END
FROM sys.sql_logins
WHERE TYPE = 'S'
AND is_disabled = 0
AND [sid] IN (SELECT [sid]
FROM #sids)
OPEN acursor
FETCH acursor INTO @login, @defdb, @chkpol, @chkexp
WHILE @@FETCH_STATUS = 0
BEGIN
/* Password Hash */
SELECT @binvalue = CAST(loginproperty(@login, 'passwordhash') AS VARBINARY(256)) ;
WITH bintab([INT], [pos], [div], [mod], [vals])
AS (SELECT CAST(SUBSTRING(@binvalue, 1, 1) AS INT),
1,
CAST(FLOOR(CAST(SUBSTRING(@binvalue, 1, 1) AS INT) / 16) AS INT),
CAST((CAST(SUBSTRING(@binvalue, 1, 1) AS INT) % 16) AS INT),
CAST('0123456789ABCDEF' AS CHAR(16))
UNION ALL
SELECT CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT),
b.[pos] + 1,
CAST(FLOOR(CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT) / 16) AS INT),
CAST((CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT) % 16) AS INT),
CAST('0123456789ABCDEF' AS CHAR(16))
FROM bintab b
WHERE b.[pos] < DATALENGTH(@binvalue)),
vals
AS (SELECT [pos],
SUBSTRING([vals], [div] + 1, 1) + SUBSTRING([vals], [mod] + 1, 1) AS [val]
FROM bintab),
cte
AS (SELECT pos,
CAST('0x' + val AS VARCHAR(514)) AS val
FROM vals
WHERE pos = 1
UNION ALL
SELECT vals.pos,
CAST(cte.val + vals.val AS VARCHAR(514))
FROM cte
JOIN vals ON cte.pos = vals.pos - 1)
SELECT TOP 1
@pwdhash = val
FROM cte
ORDER BY pos DESC
OPTION (MAXRECURSION 0) ;
/* SID Hash */
SELECT @binvalue = sid
FROM sys.server_principals
WHERE name = @login ;
WITH bintab([INT], [pos], [div], [mod], [vals])
AS (SELECT CAST(SUBSTRING(@binvalue, 1, 1) AS INT),
1,
CAST(FLOOR(CAST(SUBSTRING(@binvalue, 1, 1) AS INT) / 16) AS INT),
CAST((CAST(SUBSTRING(@binvalue, 1, 1) AS INT) % 16) AS INT),
CAST('0123456789ABCDEF' AS CHAR(16))
UNION ALL
SELECT CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT),
b.[pos] + 1,
CAST(FLOOR(CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT) / 16) AS INT),
CAST((CAST(SUBSTRING(@binvalue, b.[pos] + 1, 1) AS INT) % 16) AS INT),
CAST('0123456789ABCDEF' AS CHAR(16))
FROM bintab b
WHERE b.[pos] < DATALENGTH(@binvalue)),
vals
AS (SELECT [pos],
SUBSTRING([vals], [div] + 1, 1) + SUBSTRING([vals], [mod] + 1, 1) AS [val]
FROM bintab),
cte
AS (SELECT pos,
CAST('0x' + val AS VARCHAR(514)) AS val
FROM vals
WHERE pos = 1
UNION ALL
SELECT vals.pos,
CAST(cte.val + vals.val AS VARCHAR(514))
FROM cte
JOIN vals ON cte.pos = vals.pos - 1)
SELECT TOP 1
@sidhash = val
FROM cte
ORDER BY pos DESC
OPTION (MAXRECURSION 0) ;
INSERT #tmp
VALUES ('PRINT ''Creating login ' + QUOTENAME(@login) + '''
IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE name = ''' + @login + ''')
CREATE LOGIN ' + QUOTENAME(@login) + ' WITH password = ' + @pwdhash + ' hashed' + ', SID = ' + @sidhash
+ ', DEFAULT_DATABASE = ' + QUOTENAME(@defdb) + ', CHECK_POLICY = ' + @chkpol + ', CHECK_EXPIRATION = '
+ @chkexp + '
ELSE
PRINT ''Login ' + QUOTENAME(@login) + ' exists.''
GO')
FETCH acursor INTO @login, @defdb, @chkpol, @chkexp
END
CLOSE acursor
DEALLOCATE acursor
INSERT #tmp
SELECT 'PRINT ''Creating login ' + QUOTENAME(name) + '''
IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE name = ''' + name + ''')
CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = '
+ CASE @set_master_as_default
WHEN 0 THEN QUOTENAME(default_database_name)
ELSE QUOTENAME('master')
END
--quotename(default_database_name)
+ '
ELSE
PRINT ''Login ' + QUOTENAME(name) + ' exists.''
GO'
FROM sys.server_principals
WHERE TYPE IN ('U', 'G')
AND is_disabled = 0
AND [sid] IN (SELECT [sid]
FROM #sids)
SELECT *
FROM #tmp
WHERE [/* Script Enabled Logins */] IS NOT NULL
DROP TABLE #tmp
DROP TABLE #sids