Solved

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

Posted on 2013-05-13
8
455 Views
Last Modified: 2013-05-13
Hi,
Is there a script to copy Logins/Mapped Users/Rights/User roles/object rights from one user to another?

Basically I am in the process of replacing all SQL Accounts for each user/developer. Doing it manually is going to take a lot of time as there are more than 100 logins to be replaced on number of instances

Thanks
0
Comment
Question by:crazywolf2010
  • 4
  • 4
8 Comments
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39160925
0
 

Author Comment

by:crazywolf2010
ID: 39160951
Hi,
This one will drop and create all users,logins and privs.

How can I do this for a selective login/user for example , drop user called "monitor" but before I do so, I need to copy entire script for it.

Thanks
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39160967
i Found this http://www.sqlservercentral.com/Forums/Topic463920-5-1.aspx

It might get you sorted

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

Open in new window

0
 

Author Comment

by:crazywolf2010
ID: 39161005
This one is brilliant . One small query I am having error as below while running the proc above.

use master
go
exec USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN @OLDLOGIN = 'tester',@NEWLOGIN= 'tester123',@NEWUSER = 'tester123', @PASSWORD = 'tasbshr5!!!'
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:crazywolf2010
ID: 39161170
Sorry I missed the error

Msg 213, Level 16, State 7, Line 2
Column name or number of supplied values does not match table definition.
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39161228
Hi crazywolf2010,

This is the full explanation of the scrip

http://sqlmag.com/sql-server/generating-new-sql-server-logins
0
 

Author Comment

by:crazywolf2010
ID: 39161274
Hi,
The details mention "The stored procedure works with SQL Server 2000 and 7.0 and with both SQL Server and Windows NT authentication."

I am on SQL2008. The procedure compiled OK but returns error even after right parameters.

Thanks
0
 
LVL 7

Accepted Solution

by:
Ross Turner earned 500 total points
ID: 39161467
i found the same one but for 2008

give it a whirl

http://connectsql.wordpress.com/category/security-management/

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

Open in new window

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now