Need a script to create a new user based on existing user's permission

Question

Post reply

Need a script to create a new user based on existing user's permission

otian

Old Hand

Points: 319
More actions
March 4, 2008 at 10:35 am

#70821

Does anyone has a script can that? I am new to MS SQL world.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I 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.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

otian Old Hand Points: 319 More actions · Answer 2

It is MS SQL 2000. The Enterprise Manager can do that.

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

otian (3/4/2008)
It is MS SQL 2000. The Enterprise Manager can do that.

.

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.

SQL DBA.

otian Old Hand Points: 319 More actions · Answer 4

Unfortunately, we are still under SQL 7 and 2000. Does anyone has a script can do so?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

otian (3/4/2008)
It is MS SQL 2000. The Enterprise Manager can do that.

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 ...

Linda-272642 Ten Centuries Points: 1100 More actions · Answer 6

Try this one

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