Technical Article

Restore Server/Database users/roles

,

The script is a 3 part script that will generate another script that we will be using to restore the server/database log-ins/roles. You can run the generated script as a whole or for best results you can run it part by part.

1st PART

1st part that is generated is called "Generate scrip for server log-ins" before running this, you must supplement the neccessary password because as a default it will generate <password here> in the password section.

2nd PART

Second part is called "Generate script for server roles" which is the role of the server log-ins

3rd PART

Third part is called "Generate script for database user/roles" which is the database's users and roles

NOTE: Again I will repeat this script will also generate another script

After running the script (in the old server before the restoration) copy the result (result is another script) and run it to the server where youre stored your databases but before running this to the restore location please be reminded about the issue in orphaned users. You must delete all orphaned users first for the script in deleting orphaned users refer to this:

/***********************************

Script Made by: Lester A. Policarpio

Any Suggestions and Clarifications feel free

to email me at lpolicarpio2005@yahoo.com

This script will delete orphaned users in all

databases of a certain server

***********************************/

SET NOCOUNT ON

IF EXISTS (SELECT name from sysobjects where name = 'orphaned')

DROP TABLE orphaned

CREATE TABLE orphaned (DBName varchar(500), UserName varchar(250))

DECLARE @db varchar(500)

DECLARE @set varchar(1024)

DECLARE db CURSOR FOR

SELECT name FROM master..sysdatabases WHERE name NOT IN

('master','model','msdb','tempdb','pubs')--i didnt include the sysdatabases

OPEN db

FETCH NEXT FROM db INTO @db

WHILE (@@FETCH_STATUS = 0)

BEGIN

--SELECT statement below is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615

SET @set = 'SELECT UserName = name,'+''''+@db+''''+' as '+'''DBName'''+' FROM '+@db+'..sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0)

and suser_sname(sid) is null

ORDER BY name'

INSERT INTO orphaned(UserName,DBName) exec (@set)

FETCH NEXT FROM db INTO @db

END

CLOSE db

DEALLOCATE db

--Second CURSOR

DECLARE @db2 varchar(500)

DECLARE @db3 varchar(500)

DECLARE @change_login varchar(50)

print 'Orphaned Users are : '

DECLARE db2 CURSOR FOR

SELECT DBName,UserName FROM orphaned

OPEN db2

FETCH NEXT FROM db2 INTO @db2,@db3

WHILE (@@FETCH_STATUS = 0)

BEGIN

SET @change_login = @db2+'..sp_revokedbaccess '+''''+@db3+''''

print '['+(@db3)+']'+' -------------------- in database: '+'['+(@db2)+']' -- lets you view first the orphaned users

--exec (@change_login) --execute this part and it will delete the orphaned users

FETCH NEXT FROM db2 INTO @db2,@db3

END

CLOSE db2

DEALLOCATE db2

DROP TABLE orphaned

SET NOCOUNT OFF

/****************************************
Script Made by Lester A. Policarpio
For questions and clarifications feel free to email me at
lpolicarpio2005@yahooo.com
*/
SET NOCOUNT ON

print '--##############################################################'
print '--Generate Script for Server Log-ins'
print '--Supply <password here> with your Server log-in password'
print '--##############################################################'
print ''

DECLARE @name varchar(200)
DECLARE @default varchar(200)

print 'DECLARE @login varchar(1024)'
print 'DECLARE @q varchar(1024)'
print ''
print 'CREATE TABLE login'
print '('
print 'names varchar(124),'
print 'pass varchar(124),'
print 'db varchar(124),'
print ')'

--GENERATE SERVER LOG-IN
DECLARE master CURSOR FOR
SELECT name,dbname FROM master..syslogins WHERE name not LIKE '%BUILTIN%' AND name <> 'sa'
OPEN master
FETCH NEXT FROM master INTO @name,@default
WHILE (@@FETCH_STATUS = 0)
BEGIN
print 'INSERT INTO login VALUES ('+''''+@name+''''+','+'''<password here>'''+','+''''+@default+''''+')'
FETCH NEXT FROM master INTO @name,@default
END
CLOSE master
DEALLOCATE master

print 'DECLARE logins CURSOR FOR'

print 'select '+''''''''''+'+names+'+''''''''''+'+'','''+'+'+''''''''''+'+pass+'+''''''''''+'+'','''+'+'+''''''''''+'+db+'+''''''''''+ ' AS '+'''LOG'''+ ' FROM login'

print 'OPEN logins'

print 'FETCH NEXT FROM logins INTO @login'

print 'WHILE (@@FETCH_STATUS = 0)'

print 'BEGIN'

print 'SET @q = ''sp_addlogin ''+@login'

print 'EXEC (@q)'

print 'FETCH NEXT FROM logins INTO @login'

print 'END'

print 'CLOSE logins'

print 'DEALLOCATE logins'

print 'DROP TABLE login'

print '--#################################################################'
print '--Generate Script for Server Roles'
print '--#################################################################'
print ''


DECLARE @logins varchar(200)
DECLARE @Default1 varchar(200)
DECLARE @sysadmin int
DECLARE @securityadmin int
DECLARE @serveradmin int
DECLARE @setupadmin int
DECLARE @processadmin int
DECLARE @diskadmin int
DECLARE @dbcreator int
DECLARE @bulkadmin int
DECLARE @master int

print 'DECLARE @login2 varchar(1024)'
print 'DECLARE @w varchar(1024)'
print ''
print 'CREATE TABLE login2'
print '('
print 'names varchar(1024),'
print 'role varchar(3000)'
print ')'
print ''
print ''
DECLARE master CURSOR FOR
select name,dbname,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin
FROM master..syslogins WHERE name not LIKE 'BUILTIN%' AND name <> 'sa'
OPEN master
FETCH NEXT FROM master INTO @logins,@default1,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin
WHILE (@@FETCH_STATUS = 0)
BEGIN
--@@@@@@ sysadmin
IF (@sysadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''sysadmin'''+')'
print ''
END
--@@@@@ securityadmin
IF (@securityadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''securityadmin'''+')'
print ''
END
--@@@@@ serveradmin
IF (@serveradmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''serveradmin'''+')'
print ''
END
--@@@@@ setupadmin
IF (@setupadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''setupadmin'''+')'
print ''
END
--@@@@@ processadmin
IF (@processadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''processadmin'''+')'
print ''
END
--@@@@@ diskadmin
IF (@diskadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''diskadmin'''+')'
print ''
END
--@@@@@ dbcreator
IF (@dbcreator = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''dbcreator'''+')'
print ''
END
--@@@@@ bulkadmin
IF (@bulkadmin = 1)
BEGIN
print 'INSERT INTO login2 VALUES ('+''''+@logins+''''+','+'''bulkadmin'''+')'
print ''
END

FETCH NEXT FROM master INTO @logins,@default1,@sysadmin,@securityadmin,@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin

END

CLOSE master

DEALLOCATE master

print 'DECLARE logins CURSOR FOR'

print 'select names + '+''','''+'+ role FROM login2'

print 'OPEN logins'

print 'FETCH NEXT FROM logins INTO @login2'

print 'WHILE (@@FETCH_STATUS = 0)'

print 'BEGIN'

print 'SET @w = ''sp_addsrvrolemember ''+@login2'
print 'EXEC (@w)'

print 'FETCH NEXT FROM logins INTO @login2'

print 'END'

print 'CLOSE logins'

print 'DEALLOCATE logins'

print 'DROP TABLE login2'


print '--#################################################################'
print '--Generate Script for Database Users/ROles'
print '--#################################################################'
print ''

/****************************************
Script Made by Lester A. Policarpio
For questions and clarifications feel free to email me at
lpolicarpio2005@yahooo.com
*/DECLARE @dbcomp varchar(1024)
DECLARE @pass varchar(5000)
DECLARE @counter varchar(500)

DECLARE @dbid varchar(100)

CREATE TABLE DBROLES 
( DBName sysname not null, 
 UserName sysname not null, 
 db_owner varchar(3) not null,
 db_accessadmin varchar(3) not null,
 db_securityadmin varchar(3) not null,
 db_ddladmin varchar(3) not null,
 db_datareader varchar(3) not null,
 db_datawriter varchar(3) not null,
 db_denydatareader varchar(3) not null,
 db_denydatawriter varchar(3) not null,
 db_backupoperator varchar(3) not null
)


declare @dbname varchar(200)
declare @mSql1    varchar(8000)

DECLARE DBName_Cursor CURSOR FOR 
select name 
    from    master.dbo.sysdatabases 
    where name not in ('master','tempdb','model','pubs','northwind','DBA','msdb')
    Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
 Set @mSQL1 = '    Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin, 
 db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
     db_denydatareader, db_denydatawriter,db_backupoperator )
    SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+    '    
 Max(CASE RoleName WHEN ''db_owner''      THEN ''Yes'' ELSE ''No'' END) AS db_owner,
     Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
     Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
     Max(CASE RoleName WHEN ''db_ddladmin''      THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
     Max(CASE RoleName WHEN ''db_datareader''      THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
     Max(CASE RoleName WHEN ''db_datawriter''      THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
 Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
     Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,
    Max(CASE RoleName WHEN ''db_backupoperator'' THEN ''Yes'' ELSE ''No'' END) AS db_backupoperator
    from (
 select b.name as USERName, c.name as RoleName 
     from ' + @dbName+'.dbo.sysmembers a '+char(13)+ 
            '    join '+ @dbName+'.dbo.sysusers b '+char(13)+
     '    on a.memberuid = b.uid     join '+@dbName +'.dbo.sysusers c
     on a.groupuid = c.uid )s     
         Group by USERName 
 order by UserName'

 --Print @mSql1
 Execute (@mSql1)

 FETCH NEXT FROM DBName_Cursor INTO @dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor

DECLARE @db varchar(1024)
DECLARE @name1 varchar(200)
DECLARE @name2 varchar(20)
DECLARE @hasdbaccess varchar(200)
DECLARE @islogin varchar(200)
DECLARE @isntname varchar(200)
DECLARE @isntgroup varchar(200)
DECLARE @isntuser varchar(200)
DECLARE @issqluser varchar(200)
DECLARE @isaliased varchar(200)
DECLARE @issqlrole varchar(200)
DECLARE @isapprole varchar(200)


SET @name2 = '1'

DECLARE cur CURSOR FOR
select dbname,username,db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_datareader,db_datawriter,
db_denydatareader,db_denydatawriter,db_backupoperator from DBROLES WHERE username <> 'DBO'
OPEN cur
FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapprole
WHILE (@@FETCH_STATUS = 0)
BEGIN
print '--@@@@@'+@name1+@name2+'@@@@@--'
print 'DECLARE @'+@name1+@name2+' varchar(1024)'
print 'DECLARE @'+@name1+@name2+'2 varchar(1024)'
print 'DECLARE @'+@name1+@name2+'3 varchar(1024)'
print 'DECLARE '+@name1 +@name2+ ' CURSOR for'
print 'select name from master..sysdatabases where name IN ('+''''+@counter+''''+')'
print 'OPEN '+@name1+@name2
print 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2
print 'WHILE (@@FETCH_STATUS = 0)'
print 'BEGIN'
print 'SET @'+@name1+@name2+'2 = @'+@name1+@name2+'+'+''''+'..sp_grantdbaccess '+''''+'+'+''''+@name1+''''
print 'EXEC (@'+@name1+@name2+'2)'

-- @hasdbaccess
IF (@hasdbaccess = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_owner'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @islogin
IF (@islogin ='YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_accessadmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntname
IF (@isntname = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_securityadmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntgroup
IF (@isntgroup ='YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_ddladmin'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isntuser
IF (@isntuser = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datareader'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @issqluser
IF (@issqluser = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datawriter'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isaliased
IF (@isaliased = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatareader'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @issqlrole
IF (@issqlrole = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatawriter'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END

-- @isqpprole
IF (@isapprole = 'YES')
BEGIN
print 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_backupoperator'+','+@name1+''''
print 'EXEC (@'+@name1+@name2+'3)'
END


print 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2
print 'END'
print 'CLOSE '+@name1+@name2
print 'DEALLOCATE '+@name1+@name2

SET @name2 = @name2+'1'

FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapprole
END
CLOSE cur
DEALLOCATE cur



DROP TABLE DBROLES

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (2)

You rated this post out of 5. Change rating

Related content

External Article

Selecting the database recovery model to ensure proper backups

One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.

2008-05-30

4,151 reads