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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Need a script to create a new user based on...

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

Rate Topic

Display Mode

Topic Options

Author

Message

otian

Posted Tuesday, March 04, 2008 10:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2008 1:02 PM
Points: 3, Visits: 5

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

Thanks.

Post #463920

Lowell

Posted Tuesday, March 04, 2008 10:48 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 11,645, Visits: 27,738

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #463931

otian

Posted Tuesday, March 04, 2008 11:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2008 1:02 PM
Points: 3, Visits: 5

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

Post #463942

SanjayAttray

Posted Tuesday, March 04, 2008 11:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #463945

otian

Posted Tuesday, March 04, 2008 11:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2008 1:02 PM
Points: 3, Visits: 5

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

Post #463982

SQL ORACLE

Posted Tuesday, March 04, 2008 7:25 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314

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

Post #464177

Linda-272642

Posted Wednesday, March 05, 2008 1:56 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, August 10, 2009 2:25 PM
Points: 80, Visits: 110

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

Post #464736

« Prev Topic | Next Topic »

Permissions