Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore Server/Database users/roles


Restore Server/Database users/roles

Author
Message
Lester Policarpio
Lester Policarpio
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 2459
Comments posted to this topic are about the item Restore Server/Database users/roles

"-=Still Learning=-"

Lester Policarpio
Atiq Ur Rahman
Atiq Ur Rahman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 16
I'm migrating databases from SQL Server 2005 Standard edition to SQL Server 2008 Web edition. I successfully migrated DB users and Server Roles, but I'm facing issues to migrate Server/ Database user Roles to new server, I tried scripts in five parts as below:
- Create Database bulk backup through script on SQL Server 2005
- Restore bulk databases on SQL Server 2008 Web edition
- Run Database user backup script on SQL Server 2005 (that generates another script that I run on SQL Server 2008 to restore all database users)
- Run Server Roles backup script on SQL Server 2005 (that generates another script that I run on SQL Server 2008 to restore server roles)
- Run Server/ Database User / Roles script on SQL Server 2005 (that will generate another script to run on SQL Server 2008 to restore database / user roles). At this point I'm failing and getting errors. The scripts is as below:
Can some one help me to review and fix / compliant this script with SQL Server 2005 and fix returning script and compliant with SQL Server 2008

SQL Server Database user/ Role restoration script


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


Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8590 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search