IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_DB_CloneLogin') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.CP_DB_CloneLogin 
END
GO

CREATE PROCEDURE dbo.CP_DB_CloneLogin
  @NewLogin sysname,
  @NewLoginPwd NVARCHAR(MAX),
  @WindowsLogin CHAR(1),
  @LoginToClone sysname
AS BEGIN

SET NOCOUNT ON;

DECLARE @SQL nvarchar(MAX);
DECLARE @Men varchar(MAX);
DECLARE @Return int;
DECLARE @Ver DECIMAL(5,2);

SET @Ver = dbo.CF_DB_Version();

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @NewLogin AND type_desc = 'SQL_LOGIN')
BEGIN
	RETURN(0);
END

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @LoginToClone AND type_desc = 'SQL_LOGIN')
BEGIN
	SET @Men = 'Error encountered creating login: ' + @LoginToClone +' doesn´t exist.'
	RAISERROR(@Men, 16, 1);
	RETURN(1);
END

IF (@WindowsLogin = 'T')
	SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] FROM WINDOWS;'
ELSE
	SET @SQL = 'CREATE LOGIN [' + @NewLogin + '] WITH PASSWORD = N''' + @NewLoginPwd + ''';';

IF @Ver >= 12
	BEGIN TRAN;

PRINT @SQL;
EXEC @Return = sp_executesql @SQL;

IF (@Return <> 0)
BEGIN
	IF @Ver >= 12
		ROLLBACK TRAN;
	RAISERROR('Error encountered creating login', 16, 1);
	RETURN(1);
END


-- Query to handle server roles
DECLARE cursRoleMemberSQL CURSOR FAST_FORWARD
FOR
SELECT 'EXEC sp_addsrvrolemember @loginame = ''' + @NewLogin 
			+ ''', @rolename = ''' + R.name + ''';' AS 'SQL'
FROM sys.server_role_members AS RM
	JOIN sys.server_principals AS L
	ON RM.member_principal_id = L.principal_id
	JOIN sys.server_principals AS R
	ON RM.role_principal_id = R.principal_id
WHERE L.name = @LoginToClone;

OPEN cursRoleMemberSQL;

FETCH FROM cursRoleMemberSQL INTO @SQL;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	PRINT @SQL;
	EXECUTE @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
		IF @Ver >= 12
			ROLLBACK TRAN;
		RAISERROR('Error encountered assigning role memberships.', 16, 1);
		CLOSE cursRoleMembersSQL;
		DEALLOCATE cursRoleMembersSQL;
		RETURN(1);
	END

	FETCH NEXT FROM cursRoleMemberSQL INTO @SQL;
END;

CLOSE cursRoleMemberSQL;
DEALLOCATE cursRoleMemberSQL;

DECLARE cursServerPermissionSQL CURSOR FAST_FORWARD
FOR
SELECT CASE P.state WHEN 'W' THEN 
			'USE master; GRANT ' + P.permission_name + ' TO [' + @NewLogin + '] WITH GRANT OPTION;'
		ELSE 
			'USE master;  ' + P.state_desc + ' ' + P.permission_name + ' TO [' + @NewLogin + '];'   
		END AS 'SQL'
FROM sys.server_permissions AS P
	JOIN sys.server_principals AS L
	ON P.grantee_principal_id = L.principal_id
WHERE L.name = @LoginToClone
	AND P.class = 100
	AND P.type <> 'COSQ'
UNION ALL
SELECT CASE P.state WHEN 'W' THEN 
			'USE master; GRANT ' + P.permission_name + ' ON LOGIN::[' + L2.name + 
			'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
		ELSE 
			'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON LOGIN::[' + L2.name 
			+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
		END AS 'SQL'
FROM sys.server_permissions AS P
	JOIN sys.server_principals AS L
	ON P.grantee_principal_id = L.principal_id
	JOIN sys.server_principals AS L2
	ON P.major_id = L2.principal_id
WHERE L.name = @LoginToClone
	AND P.class = 101
UNION ALL
SELECT CASE P.state WHEN 'W' THEN 
			'USE master; GRANT ' + P.permission_name + ' ON ENDPOINT::[' + E.name + 
			'] TO [' + @NewLogin + '] WITH GRANT OPTION;' COLLATE DATABASE_DEFAULT
		ELSE 
			'USE master; ' + P.state_desc + ' ' + P.permission_name + ' ON ENDPOINT::[' + E.name 
			+ '] TO [' + @NewLogin + '];' COLLATE DATABASE_DEFAULT
		END AS 'SQL'
FROM sys.server_permissions AS P
	JOIN sys.server_principals AS L
	ON P.grantee_principal_id = L.principal_id
	JOIN sys.endpoints AS E
	ON P.major_id = E.endpoint_id
WHERE L.name = @LoginToClone
	AND P.class = 105;

OPEN cursServerPermissionSQL;

FETCH FROM cursServerPermissionSQL INTO @SQL;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	PRINT @SQL;
	EXEC @Return = sp_executesql @SQL;

	IF (@Return <> 0)
	BEGIN
	    IF @Ver >= 12
		   ROLLBACK TRAN;
		RAISERROR('Error encountered adding server level permissions', 16, 1);
		CLOSE cursServerPermissionSQL;
		DEALLOCATE cursServerPermissionSQL;
		RETURN(1);
	END

	FETCH NEXT FROM cursServerPermissionSQL INTO @SQL;
END;

CLOSE cursServerPermissionSQL;
DEALLOCATE cursServerPermissionSQL;

IF @Ver >= 12
	COMMIT TRAN;

PRINT 'Login [' + @NewLogin + '] cloned successfully from [' + @LoginToClone + '].';
END;