Restore Server/Database users/roles

Question

Restore Server/Database users/roles

Lester Policarpio

SSCertifiable

Points: 7109
More actions
October 25, 2007 at 12:03 pm

#199496

Comments posted to this topic are about the item Restore Server/Database users/roles
"-=Still Learning=-"
Lester Policarpio

Viewing 3 posts - 1 through 2 (of 2 total)

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

Atiq Ur Rahman Grasshopper Points: 19 More actions · Answer 1

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+''''