CREATE PROCEDURE [dbo].[ScriptLogins]

AS

SET NOCOUNT ON

DECLARE
	@StatementToRun VARCHAR(MAX) = ''
	,@CurrentRow INT
	,@TotalRows INT
	,@sid VARBINARY(85)
	,@name SYSNAME
	,@type CHAR(1)
	,@is_disabled INT
	,@default_database_name SYSNAME
	,@hasaccess INT
	,@denylogin INT
	,@password_hash VARBINARY(256)
	,@PasswordHashString NVARCHAR(300)
	,@SIDString NVARCHAR(100)
	,@is_policy_checked VARCHAR(3)
	,@is_expiration_checked VARCHAR(3)
	,@RoleName SYSNAME
	,@LoginName SYSNAME
	,@PermState NVARCHAR(60)
	,@PermName SYSNAME
	,@Class TINYINT
	,@ClassDesc NVARCHAR(60)
	,@MajorID INT
	,@SubLoginName SYSNAME
	,@SubEndPointName SYSNAME

/**********************************************************************
Usage:

Run from the instance that the logins will be created on:
DECLARE @StatementToRun VARCHAR(MAX)
SELECT @StatementToRun = StatementToRun FROM OPENQUERY("linkedservername", 'EXEC master.dbo.ScriptLogins')
EXEC (@StatementToRun)
**********************************************************************/

/**********************************************************************
Credits:

Created by Tomas Lind 2014
http://media.tomaslind.net/2014/02/ScriptLogins.txt

Most of the functionality in this procedure is copied from two sources:

Microsoft Support: sp_help_revlogin
(http://support.microsoft.com/kb/918992)

SQLSoldier: dba_CopyLogins
(http://www.sqlsoldier.com/wp/wp-content/uploads/Scripts/dba_CopyLogins.sql)

**********************************************************************/


/**********************************************************************
@sp_validatelogins
Results from sp_validatelogins is used to filter out users
no longer in AD.
**********************************************************************/
DECLARE @sp_validatelogins TABLE
    (
        [sid] VARBINARY(85) NOT NULL
        ,NTLogin SYSNAME NOT NULL
    )
 
INSERT INTO @sp_validatelogins
EXEC sp_validatelogins


/**********************************************************************
@Logins
The logins to transfer
**********************************************************************/
DECLARE @Logins TABLE
	(
		LoginId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
		,[sid] VARBINARY(85) NOT NULL
		,[name] SYSNAME NOT NULL
		,[type] CHAR(1) NOT NULL
		,is_disabled INT NOT NULL
		,default_database_name SYSNAME NOT NULL
		,hasaccess INT NOT NULL
		,denylogin INT NOT NULL
		,password_hash VARBINARY(256) NULL
		,is_policy_checked VARCHAR(3) NULL
		,is_expiration_checked VARCHAR(3) NULL
	)
INSERT
	@Logins ([sid], [name], [type], is_disabled, default_database_name, hasaccess, denylogin, password_hash, is_policy_checked, is_expiration_checked)
SELECT
	principals.[sid]
	,principals.[name]
	,principals.[type]
	,principals.is_disabled
	,principals.default_database_name
	,logins.hasaccess
	,logins.denylogin
	,sqllogins.password_hash
	,CASE sqllogins.is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
	,CASE sqllogins.is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END
FROM
	sys.server_principals AS principals
LEFT OUTER JOIN
	sys.syslogins AS logins
ON
	principals.[name] = logins.[name]
LEFT OUTER JOIN
	sys.sql_logins AS sqllogins
ON
	principals.principal_id = sqllogins.principal_id
WHERE
	principals.[type] IN ('S', 'U', 'G') --S = SQL login, U = Windows login, G = Windows group
AND
	principals.[sid] <> 0x01 --sa even if it is renamed
AND
	principals.[name] NOT LIKE '##%' --system users
AND
	principals.[name] NOT LIKE 'BUILTIN%' --system users
AND
	principals.[name] NOT LIKE 'NT %' --system users
AND
	SUBSTRING(principals.[name], 1, ABS(CHARINDEX('\', principals.[name])-1)) <> SUBSTRING(@@SERVERNAME, 1, ABS(CHARINDEX('\', @@SERVERNAME)-1)) --local machine users
AND
	principals.[name] NOT IN (SELECT NTLogin FROM @sp_validatelogins) --users no longer in AD.


/**********************************************************************
@Roles
The roles to transfer
**********************************************************************/
DECLARE @Roles TABLE
	(
		RoleId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
		,RoleName SYSNAME NOT NULL
		,LoginName SYSNAME NOT NULL
	)

INSERT
	@Roles (RoleName, LoginName)
SELECT
	role_principals.name
	,login_principals.name
FROM
	sys.server_role_members AS role_members
JOIN
	sys.server_principals AS role_principals
ON
	role_members.role_principal_id = role_principals.principal_id
JOIN
	sys.server_principals AS login_principals
ON
	role_members.member_principal_id = login_principals.principal_id
WHERE
	login_principals.[type] IN ('S', 'U', 'G') --S = SQL login, U = Windows login, G = Windows group
AND
	login_principals.[sid] <> 0x01 --sa even if it is renamed
AND
	login_principals.[name] NOT LIKE '##%' --system users
AND
	login_principals.[name] NOT LIKE 'BUILTIN%' --system users
AND
	login_principals.[name] NOT LIKE 'NT %' --system users
AND
	SUBSTRING(login_principals.[name], 1, ABS(CHARINDEX('\', login_principals.[name])-1)) <> SUBSTRING(@@SERVERNAME, 1, ABS(CHARINDEX('\', @@SERVERNAME)-1)) --local machine users
AND
	login_principals.[name] NOT IN (SELECT NTLogin FROM @sp_validatelogins) --users no longer in AD.
AND
	role_principals.[type] = 'R'


/**********************************************************************
@Permissions
The permissions to transfer
**********************************************************************/
DECLARE @Permissions TABLE
	(
		PermissionId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
		,LoginName SYSNAME NOT NULL
		,PermState NVARCHAR(60) NOT NULL
		,PermName SYSNAME NOT NULL
		,Class TINYINT NOT NULL
		,ClassDesc NVARCHAR(60) NOT NULL
		,MajorID INT NOT NULL
		,SubLoginName SYSNAME NULL
		,SubEndPointName SYSNAME NULL
	)

INSERT
	@Permissions (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
SELECT
	Tprincipals.name COLLATE database_default
	,Tpermissions.state_desc
	,Tpermissions.[permission_name]
	,Tpermissions.class
	,Tpermissions.class_desc
	,Tpermissions.major_id
	,TServerPrincipal.name COLLATE database_default
	,Tendpoints.name COLLATE database_default
FROM
	sys.server_principals AS Tprincipals
JOIN
	sys.server_permissions AS Tpermissions
ON
	Tprincipals.principal_id = Tpermissions.grantee_principal_id
LEFT OUTER JOIN
	sys.server_principals AS TServerPrincipal
ON
	TServerPrincipal.principal_id = Tpermissions.major_id
AND
	Tpermissions.class = 101
LEFT OUTER JOIN
	sys.endpoints AS Tendpoints
ON
	Tendpoints.endpoint_id = Tpermissions.major_id
AND
	Tpermissions.class = 105
WHERE
	Tprincipals.[type] IN ('S', 'U', 'G') --S = SQL login, U = Windows login, G = Windows group
AND
	Tprincipals.[sid] <> 0x01 --sa even if it is renamed
AND
	Tprincipals.[name] NOT LIKE '##%' --system users
AND
	Tprincipals.[name] NOT LIKE 'BUILTIN%' --system users
AND
	Tprincipals.[name] NOT LIKE 'NT %' --system users
AND
	SUBSTRING(Tprincipals.[name], 1, ABS(CHARINDEX('\', Tprincipals.[name])-1)) <> SUBSTRING(@@SERVERNAME, 1, ABS(CHARINDEX('\', @@SERVERNAME)-1)) --local machine users
AND
	Tprincipals.[name] NOT IN (SELECT NTLogin FROM @sp_validatelogins) --users no longer in AD.


/**********************************************************************
Add the logins to the script
**********************************************************************/
SELECT @TotalRows = COUNT(*), @CurrentRow = 1 FROM @Logins

WHILE @CurrentRow <= @TotalRows BEGIN

	SELECT @sid = [sid] ,@name = [name] ,@type = [type] ,@is_disabled = is_disabled ,@default_database_name = default_database_name ,@hasaccess = hasaccess, @denylogin = denylogin, @password_hash = password_hash, @is_policy_checked = is_policy_checked, @is_expiration_checked = is_expiration_checked
	FROM @Logins
	WHERE LoginId = @CurrentRow

	IF (@type IN ('G', 'U')) BEGIN --NT authenticated account/group
		SET @StatementToRun = @StatementToRun +
			CHAR(13) + CHAR(13) + '
			IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''') BEGIN
				DROP LOGIN ' + QUOTENAME(@name) + '
			END
			IF EXISTS (SELECT * FROM sys.databases WHERE [name] = ''' + @default_database_name + ''') BEGIN
				CREATE LOGIN ' + QUOTENAME(@name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @default_database_name + ']' + '
			END
			ELSE BEGIN
				CREATE LOGIN ' + QUOTENAME(@name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [master]
			END'
	END
    ELSE BEGIN --SQL Server authentication
		SET @PasswordHashString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@password_hash"))', 'nvarchar(300)')
		SET @SIDString = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable("@sid"))', 'nvarchar(100)')

		SET @StatementToRun = @StatementToRun +
			CHAR(13) + CHAR(13) + '
			IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''') BEGIN
				DROP LOGIN ' + QUOTENAME(@name) + '
			END
			IF EXISTS (SELECT * FROM sys.databases WHERE [name] = ''' + @default_database_name + ''') BEGIN
				CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PasswordHashString + ' HASHED, SID = ' + @SIDString + ', DEFAULT_DATABASE = [' + @default_database_name + ']' + '
			END
			ELSE BEGIN
				CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PasswordHashString + ' HASHED, SID = ' + @SIDString + ', DEFAULT_DATABASE = [master]
			END'

			IF (@is_policy_checked IS NOT NULL) BEGIN
				SET @StatementToRun = @StatementToRun + '
			ALTER LOGIN ' + QUOTENAME(@name) + ' WITH CHECK_POLICY = ' + @is_policy_checked
			END
			IF (@is_expiration_checked IS NOT NULL) BEGIN
				SET @StatementToRun = @StatementToRun + ', CHECK_EXPIRATION = ' + @is_expiration_checked
			END

	END

	IF (@denylogin = 1) BEGIN -- login is denied access
		SET @StatementToRun = @StatementToRun + '; DENY CONNECT SQL TO ' + QUOTENAME(@name)
    END
    ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access
		SET @StatementToRun = @StatementToRun + '; REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
    END
    IF (@is_disabled = 1) BEGIN -- login is disabled
		SET @StatementToRun = @StatementToRun + '
			ALTER LOGIN ' + QUOTENAME(@name) + ' DISABLE'
    END

	SET @CurrentRow = @CurrentRow + 1
END


/**********************************************************************
Add the roles to the script
**********************************************************************/
SELECT @TotalRows = COUNT(*), @CurrentRow = 1 FROM @Roles

WHILE @CurrentRow <= @TotalRows BEGIN
	SELECT @RoleName = RoleName, @LoginName = LoginName
	FROM @Roles
	WHERE RoleId = @CurrentRow

	SET @StatementToRun = @StatementToRun + 
	CHAR(13) + '
			EXEC sp_addsrvrolemember @rolename = ' + @RoleName + ', @loginame = ' + QUOTENAME(@LoginName)

	SET @CurrentRow = @CurrentRow + 1
END


/**********************************************************************
Add the permisssions to the script
**********************************************************************/
SELECT @TotalRows = COUNT(*), @CurrentRow = 1 FROM @Permissions

WHILE @CurrentRow <= @TotalRows BEGIN
	SELECT @LoginName = LoginName, @PermState = PermState, @PermName = PermName, @Class = Class, @ClassDesc = ClassDesc, @MajorID = MajorID, @SubLoginName = SubLoginName, @SubEndPointName = SubEndPointName
	FROM @Permissions
	WHERE PermissionId = @CurrentRow

	SET @StatementToRun = @StatementToRun + 
	CHAR(13) + '
			' + @PermState + ' ' + @PermName + ' ' +
			CASE @Class WHEN 101 THEN 'ON LOGIN::' + QUOTENAME(@SubLoginName) WHEN 105 THEN 'ON ' + @ClassDesc + '::' + QUOTENAME(@SubEndPointName)  ELSE '' END +
			' TO ' + QUOTENAME(@LoginName)

	SET @CurrentRow = @CurrentRow + 1
END

SELECT @StatementToRun AS StatementToRun
GO