Copy Logins rights/user roles/object rights from one user to another

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO






/****** Object: Stored Procedure dbo.USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN Script Date: 10/21/2002 6:56:14 AM ******/




CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN 
@OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME
@NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME
@NEWUSER CHAR(128) , -- NEW USER NAME FOR EACH DATABASE
@PASSWORD VARCHAR(200) ='' -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
AS
-- 
-- WRITTEN BY: GREG LARSEN FOR WASHINTON STATE DEPARTMENT OF HEALTH
-- DATE: DECEMBER 13, 2001
-- 
-- DESCRIPTION: THIS STORED PROCEDURE GENERATES COMMANDS 
-- THAT WILL ADD A NEW USERS TO THE SERVER WITH THE SAME 
-- RIGHTS AS AN EXISTING USER. 
--
-- UPDATE HISTORY: 
-- DATE: OCTOBER 21, 2002 
-- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. 
--

-- BEGIN CALLOUT A
-- Declare variables and create temporary table to hold commands
-------------------------------------------------------------------------------
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)
--
-- CREATE TABLE TO HOLD GENERATED COMMANDS
--
CREATE TABLE #TMP_LOGIN_RIGHTS (
RIGHTS_TEXT CHAR(2000))

-- END CALLOUT A
-- BEGIN CALLOUT B
-- ADD USER TO SERVER
----------------------------------------------------------------------------- 
-- TEST TO SEE IF #OLDOGIN EXISTS ON SERVER
--
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
--
-- DETERMINE IF @NEWLOGIN IS ALREADY DEFINED TO 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 -- @PASSWORD = '' 
--
-- 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

-- END CALLOUT B
-- BEGIN CALLOUT C
-- ADD USER TO DATABASES
-------------------------------------------------------------------------------
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 SMALLINT,
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 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
-- END CALLOUT C
-- BEGIN CALLOUT D
-- GRANT USER TO ROLES WITHIN DATABASES
--------------------------------------------------------------------------------
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 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
-- END CALLOUT D
-- BEGIN CALLOUT E
-- GRANT USER ACCESS TO SERVER ROLES
-----------------------------------------------------------------------------
-- 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
-- END CALLOUT E
-- BEGIN CALLOUT F
-- 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 AND DEALLOCATE DATABASE LIST CURSOR
--
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
--
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
--
DROP TABLE #TMPPROTECT
-- END CALLOUT F
-- BEGIN CALLOUT G
-- PROCESS ALL GENERATED COMMANDS ONE AT A TIME
---------------------------------------------------------------------------------
--
-- 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
--
-- PRINT COMMAND TO BE PROCESSED
--
PRINT @CMD
--
-- UNCOMMENT IF YOU WANT THE STORED PROCEDURE TO EXECUTE THE COMMANDS TO ADD THE PERMISSIONS
--
--EXEC (@CMD)
--
-- GET NEXT COMMAND
--
FETCH NEXT FROM COMMANDS INTO @CMD
END -- WHILE (@@FETCH_STATUS = 0)
--
-- CLOSE AND DEALLOCATE COMMAND CURSOR
--
CLOSE COMMANDS
DEALLOCATE COMMANDS
--
-- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO @NEWLOGIN 
--
DROP TABLE #TMP_LOGIN_RIGHTS

-- END CALLOUT G



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Select allOpen in new window