SQL Server: Script to Create Replica of Existing Database User

Today, I would like to share a script, which is helpful to create a clone of an existing database user, with all of its rights/permissions and securables. This script was originally written by GREG LARSEN for SQL Server 2000, I just made necessary changes so it can be used for SQL Server 2008.
(To Copy this script, first past in MS Word document file and then copy to SQL Server)

CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN
@OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME (COPY FROM)
@NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME (COPY TO)
@NEWUSER VARCHAR(128), -- NEW USER NAME 
@PASSWORD VARCHAR(200) = '', -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
@CREATE_SCRIPT_ONLY BIT = 1 -- 1 IF TO GET ONLY TSQL SCRIPT, 0 IF WANT TO CREATE USER DIRECTLY 
AS -- 

DECLARE @INDX INT 
SET NOCOUNT ON 
DECLARE @TEXT CHAR(100) 
DECLARE @CNT INT
DECLARE @CMD NVARCHAR(200)
DECLARE @DB NVARCHAR(128) 
DECLARE @OLDUSER VARCHAR(100)
-- Temp Table to hold generated commands
CREATE TABLE #TMP_LOGIN_RIGHTS ( RIGHTS_TEXT VARCHAR(MAX) )
----------------------------------------------------------------------------- 
-- Check if given OldLogin exists
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
IF @CNT = 0 
BEGIN
RAISERROR ( '@OLDLOGIN IS NOT A VALID USER OF SQL SERVER', 16, 1 )
RETURN
END
-- Check if given NewUser allready exists on server
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @NEWLOGIN
--
-- IF @NEWLOGIN EXIST ABORT
IF @CNT > 0 
BEGIN
RAISERROR ( '@NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16, 1 ) 
RETURN
END
-- IF @NEWLOGIN CONTAINS '\' THEN NT LOGIN
SELECT  @INDX = CHARINDEX('\', @NEWLOGIN)
IF @INDX > 0 
-- GENERATE COMMANDS TO ADD NT USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_GRANTLOGIN] ''' + @NEWLOGIN
+ '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
ELSE 
BEGIN
IF @PASSWORD = '' 
BEGIN
RAISERROR ( '@PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
16, 1 ) 
RETURN
END -- 
-- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDLOGIN] '''
+ @NEWLOGIN + ''',''' + @PASSWORD + '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
END
-------------------------------------------------------------------------------
SET NOCOUNT ON 
SET @CMD = '[MASTER].[DBO].[SP_HELPUSER]'
-- GET THE NAME OF ALL DATABASES
DECLARE ALLDATABASES CURSOR
FOR SELECT  NAME
FROM    [MASTER].[DBO].[SYSDATABASES] 
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
-- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
CREATE TABLE #TMPUSERS
(
USERNAME VARCHAR(100),
GROUPNAME VARCHAR(100),
LOGINNAME VARCHAR(100),
DEFDBNAME VARCHAR(100),
USERID CHAR(10),
SCHEMANAME VARCHAR(100),
SUSERID SMALLINT
)
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- COMMAND TO RETURN ALL USERS IN DATABASE
SET @CMD = '[' + @DB + ']' + '.[DBO].[SP_HELPUSER]'
-- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,
USERID,SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER OLD USER IS IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
-- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
IF @CNT > 0 
BEGIN
-- DETERMINE IF @NEWUSER ALREADY EXIST IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   USERNAME = @NEWUSER
-- IF USER EXIST ABORT
IF @CNT > 0 
BEGIN
-- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO ERROR OCCURS
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- SET TEXT OF ERROR MESSAGE
SET @TEXT = '@NEWUSER ALREADY EXIST IN DATABASE '
+ @DB
-- RAISE ERROR AND RETURN
RAISERROR ( @TEXT, 16, 1 )
RETURN
END
-- GENERATE COMMAND TO ADD @NEWLOGIN TO CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [' + @DB
+ '].[DBO].[SP_GRANTDBACCESS] '''
+ @NEWLOGIN + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    ( SELECT DISTINCT
USERNAME,
LOGINNAME
FROM      #TMPUSERS
WHERE     LOGINNAME = @OLDLOGIN
) A
END
-- TRUNCATE TABLE FOR NEXT DATABASE
TRUNCATE TABLE #TMPUSERS
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
-- CLOSE CURSOR OF DATABASES
CLOSE ALLDATABASES
--------------------------------------------------------------------------------
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPUSER]'
-- EMPTY TEMPORARY TABLE #TMPUSERS
TRUNCATE TABLE #TMPUSERS
-- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,USERID,
SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
AND GROUPNAME <> 'PUBLIC'
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWUSER TO APPROPRIATE ROLES IN CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT DISTINCT
'EXECUTE [' + @DB
+ '].[DBO].[SP_ADDROLEMEMBER] '''
+ RTRIM(A.GROUPNAME) + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    #TMPUSERS A
WHERE   A.LOGINNAME = @OLDLOGIN
AND A.GROUPNAME <> 'PUBLIC' 
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DROP TABLE #TMPUSERS
-----------------------------------------------------------------------------
-- CREATE TABLE TO HOLD SERVER ROLES
CREATE TABLE #TMPSRVROLES
(
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY(85)
)
-- COMMAND TO GET SERVER ROLES
SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
-- GET SERVER ROLES INTO TEMPORARY TABLE
INSERT  INTO #TMPSRVROLES
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPSRVROLES
WHERE   MEMBERNAME = @OLDLOGIN
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + ''''
+ RTRIM(@NEWLOGIN) + '''' + ',[' + RTRIM(A.SERVERROLE)
+ ']' AS RIGHTS_TEXT
FROM    #TMPSRVROLES A
WHERE   A.MEMBERNAME = @OLDLOGIN 
-- DROP SERVER ROLE TABLE
DROP TABLE #TMPSRVROLES
-- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
-------------------------------------------------------------------------------
-- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
CREATE TABLE #TMPPROTECT
(
OWNER VARCHAR(100),
OBJECT VARCHAR(100),
GRANTEE VARCHAR(100),
GRANTOR VARCHAR(100),
PROTECTTYPE CHAR(10),
ACTION VARCHAR(20),
COLUMNX VARCHAR(100)
)
OPEN ALLDATABASES
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- INITIALIZE @OLDUSER VARIABLE 
SET @OLDUSER = '' 
--GENERATE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
-- EXECUTE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
EXEC [MASTER].[DBO].[SP_EXECUTESQL] @CMD,
N'@OLDUSER CHAR(200) OUTPUT', @OLDUSER OUT
-- IF @OLDUSER IS NOT BLANK THEN @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
IF @OLDUSER <> '' 
BEGIN
-- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPROTECT]'
-- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
INSERT  INTO #TMPPROTECT
EXEC ( @CMD
)
-- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @OLDUSER
SELECT  @CNT = COUNT(*)
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER 
IF @CNT > 0 
-- SWITCH TO THE APPROPRIATE DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'USE [' + @DB + ']'
-- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT, UPDATE TO @NEWUSER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND COLUMNX = '(ALL+NEW)'
-- GRANT COLUMN PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND COLUMNX <> '(ALL+NEW)'
AND COLUMNX <> '.'
-- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT <> '.'
AND ACTION IN ( 'INSERT', 'DELETE',
'EXECUTE' )
AND COLUMNX = '.'
-- GRANT STATEMENT PERMISSIONS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'GRANT ' + ACTION + ' TO ['
+ RTRIM(@NEWUSER) + ']' AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT = '.'
-- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE TO BE PROCESSES
TRUNCATE TABLE #TMPPROTECT
END
-- GET NEXT DATABASE TO PROCESS
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
DROP TABLE #TMPPROTECT
---------------------------------------------------------------------------------
-- GET ALL THE GENERATED COMMANDS
DECLARE COMMANDS CURSOR
FOR SELECT  *
FROM    #TMP_LOGIN_RIGHTS
OPEN COMMANDS
FETCH NEXT FROM COMMANDS INTO @CMD
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @CREATE_SCRIPT_ONLY = 1
PRINT @CMD
ELSE
EXEC (@CMD)
FETCH NEXT FROM COMMANDS INTO @CMD
END 
CLOSE COMMANDS
DEALLOCATE COMMANDS
--DROP TEMPORARY TABLES
DROP TABLE #TMP_LOGIN_RIGHTS