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