InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 10:58:53 GMT20http://www.sqlservercentral.com/Forums/Topic463920-5-1.aspxTry this oneSET QUOTED_IDENTIFIER OFF GOSET 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 USERSAS-- -- 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 INTDECLARE @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 = @OLDLOGINIF @CNT = 0BEGIN RAISERROR ('@OLDLOGIN IS NOT A VALID USER OF SQL SERVER',16,1) RETURNEND---- 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) RETURNEND---- 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 = @OLDLOGINELSEBEGIN 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 = @OLDLOGINEND-- 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 FORSELECT NAME FROM [MASTER].[DBO].[SYSDATABASES] OPEN ALLDATABASESFETCH 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 @DBEND -- WHILE (@@FETCH_STATUS = 0)---- CLOSE CURSOR OF DATABASES--CLOSE ALLDATABASES-- END CALLOUT C-- BEGIN CALLOUT D-- GRANT USER TO ROLES WITHIN DATABASES--------------------------------------------------------------------------------OPEN ALLDATABASESFETCH NEXT FROM ALLDATABASES INTO @DBWHILE (@@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 @DBEND -- WHILE (@@FETCH_STATUS = 0)CLOSE ALLDATABASESDROP 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 ALLDATABASESSET @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 @DBWHILE (@@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 #TMPPROTECTEND---- GET NEXT DATABASE TO PROCESS--FETCH NEXT FROM ALLDATABASES INTO @DBEND -- WHILE (@@FETCH_STATUS = 0)---- CLOSE AND DEALLOCATE DATABASE LIST CURSOR--CLOSE ALLDATABASESDEALLOCATE 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 FORSELECT * FROM #TMP_LOGIN_RIGHTS OPEN COMMANDSFETCH NEXT FROM COMMANDS INTO @CMDWHILE (@@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 @CMDEND -- WHILE (@@FETCH_STATUS = 0)---- CLOSE AND DEALLOCATE COMMAND CURSOR--CLOSE COMMANDSDEALLOCATE COMMANDS---- DROP TABLE THAT HELD THE GENERATED RIGHTS THAT WHERE GRANTED TO @NEWLOGIN --DROP TABLE #TMP_LOGIN_RIGHTS-- END CALLOUT GGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOWed, 05 Mar 2008 13:56:29 GMTLinda-272642http://www.sqlservercentral.com/Forums/Topic463920-5-1.aspx[quote][b]otian (3/4/2008)[/b][hr]It is MS SQL 2000. The Enterprise Manager can do that.[/quote]Yes, you are right. SQL 2000 can do it in a similar approach as SQL 2005.Generate Scripts, choose option, include users, ...;Preview scripts;Copy ...Tue, 04 Mar 2008 19:25:10 GMTSQL ORACLEhttp://www.sqlservercentral.com/Forums/Topic463920-5-1.aspxUnfortunately, we are still under SQL 7 and 2000. Does anyone has a script can do so?Tue, 04 Mar 2008 11:55:44 GMTotianhttp://www.sqlservercentral.com/Forums/Topic463920-5-1.aspx[quote][b]otian (3/4/2008)[/b][hr]It is MS SQL 2000. The Enterprise Manager can do that.[/quote].No. Enterprise Manager in 2000 is not that robust. But, you can register your 2000 server in 2005 and then follow what Lowell had said. Result is same.Tue, 04 Mar 2008 11:16:10 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic463920-5-1.aspxIt is MS SQL 2000. The Enterprise Manager can do that.Tue, 04 Mar 2008 11:06:00 GMTotianhttp://www.sqlservercentral.com/Forums/Topic463920-5-1.aspxI think what you are looking for is almost built in:in MS SQL Server Management Studio, browse to Server>>Security>>Logions (NOT [specific database]>>Security)right click on the existing user you want to "copy" and select "Script Login As...">>"New Query Window"change the script just slightly: find and replace the oldlogin name with the newloginname.Tue, 04 Mar 2008 10:48:19 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic463920-5-1.aspxDoes anyone has a script can that? I am new to MS SQL world.Thanks.Tue, 04 Mar 2008 10:35:11 GMTotian