October 25, 2007 at 12:03 pm
Comments posted to this topic are about the item Restore Server/Database users/roles
"-=Still Learning=-"
Lester Policarpio
January 1, 2011 at 8:23 am
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
May 10, 2016 at 1:52 pm
Thanks for the script.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy