InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:04:34 GMT20http://www.sqlservercentral.com/Forums/Topic415065-570-1.aspxI'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[b]SQL Server Database user/ Role restoration script[/b][code="other"]print '--#################################################################'print '--Generate Script for Database Users/Roles'print '--#################################################################'print ''/****************************************Script Made by Lester A. PolicarpioFor questions and clarifications feel free to email me atlpolicarpio2005@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 nameOPEN DBName_CursorFETCH NEXT FROM DBName_Cursor INTO @dbnameWHILE @@FETCH_STATUS = 0BEGIN 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_backupoperatorfrom ( select b.name as USERName, c.name as RoleNamefrom ' + @dbName+'.dbo.sysmembers a '+char(13)+' join '+ @dbName+'.dbo.sysusers b '+char(13)+' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers con a.groupuid = c.uid )sGroup by USERName order by UserName' Print @mSql1 Execute (@mSql1) FETCH NEXT FROM DBName_Cursor INTO @dbnameENDCLOSE DBName_CursorDEALLOCATE DBName_CursorDECLARE @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 FORselect dbname,username,db_owner,db_accessadmin,db_securityadmin,db_ddladmin,db_datareader,db_datawriter,db_denydatareader,db_denydatawriter,db_backupoperator from DBROLES WHERE username &lt;&gt; 'DBO'OPEN curFETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapproleWHILE (@@FETCH_STATUS = 0)BEGINprint '--@@@@@'+@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+@name2print 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2print 'WHILE (@@FETCH_STATUS = 0)'print 'BEGIN'print 'SET @'+@name1+@name2+'2 = @'+@name1+@name2+'+'+''''+'..sp_grantdbaccess '+''''+'+'+''''+@name1+''''print 'EXEC (@'+@name1+@name2+'2)'-- @hasdbaccessIF (@hasdbaccess = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_owner'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isloginIF (@islogin ='YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_accessadmin'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isntnameIF (@isntname = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_securityadmin'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isntgroupIF (@isntgroup ='YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_ddladmin'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isntuserIF (@isntuser = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datareader'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @issqluserIF (@issqluser = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_datawriter'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isaliasedIF (@isaliased = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatareader'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @issqlroleIF (@issqlrole = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_denydatawriter'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'END-- @isqpproleIF (@isapprole = 'YES')BEGINprint 'SET @'+@name1+@name2+'3 = @'+@name1+@name2+'+'+''''+'..sp_addrolemember '+''''+'+'+''''+'db_backupoperator'+','+@name1+''''print 'EXEC (@'+@name1+@name2+'3)'ENDprint 'FETCH NEXT FROM '+@name1+@name2+' INTO @'+@name1+@name2print 'END'print 'CLOSE '+@name1+@name2print 'DEALLOCATE '+@name1+@name2SET @name2 = @name2+'1'FETCH NEXT FROM cur INTO @counter,@name1,@hasdbaccess,@islogin,@isntname,@isntgroup,@isntuser,@issqluser,@isaliased,@issqlrole,@isapproleENDCLOSE curDEALLOCATE curDROP TABLE DBROLES[/code]Sat, 01 Jan 2011 08:23:52 GMTAtiq Ur Rahmanhttp://www.sqlservercentral.com/Forums/Topic415065-570-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Backup+%2f+Restore/61375/">Restore Server/Database users/roles</A>[/B]Thu, 25 Oct 2007 12:03:30 GMTLester Policarpio