USE [master]GOBACKUP DATABASE [PCC] TO DISK = N'E:\Temp\PCC.BAK'WITH INIT, COPY_ONLYGORESTORE DATABASE [TSDEV] FROM DISK = N'E:\Temp\PCC.BAK'WITH REPLACE, FILE = 1,MOVE N'GPSPCCDat.mdf' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\GPSTSDEVDat.mdf',MOVE N'GPSPCCLog.ldf' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\GPSTSDEVLog.ldf',NOUNLOAD, STATS = 10GOUSE [TSDEV]GO/* Change the DBO to DYNSA */sp_changedbowner 'sa'GOsp_changedbowner 'DYNSA'GO/* Declare Variables */DECLARE @New_CmpanyID NVARCHAR(255)DECLARE @New_InterID NVARCHAR(255)DECLARE @MyStatement NVARCHAR(255)/* Assign Values to Variables */SELECT @New_CmpanyID = RTRIM(LTRIM(STR(CmpanyID))) FROM DYNAMICS..SY01500 WHERE INTERID = DB_Name()SET @New_InterID = RTRIM(DB_Name())/* Create cursor to hold UPDATE statements that will update all available CMPANYID values in the database */declare MyCursor CURSOR for select 'update ' + a.name + ' set CMPANYID = ' + @New_CmpanyID from sysobjects a, syscolumns b where b.id = a.id AND a.xtype in ('U') AND b.name = 'cmpanyid' order by 1/* Step through each row in the cursor, executing each UPDATE statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursor/* Create cursor to hold UPDATE statements that will update all available INTERID values in the database */declare MyCursor CURSOR for select 'update ' + a.name + ' set INTERID = ' + '''' + @New_InterID + '''' from sysobjects a, syscolumns b where b.id = a.id AND a.xtype in ('U') AND b.name = 'interid' order by 1/* Step through each row in the cursor, executing each UPDATE statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursor/* Create cursor to hold DROP SCHEMA statements that will drop all GP user schemata in the database */declare MyCursor CURSOR for select 'IF EXISTS (SELECT * FROM sys.schemas WHERE name = N' + '''' + RTRIM(a.name) + '''' + ') DROP SCHEMA ' + RTRIM(a.name) + ';' from sys.schemas a where a.name in (select userid from DYNAMICS..SY01400) order by 1/* Step through each row in the cursor, executing each DROP SCHEMA statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursor/* Create cursor to hold sp_revokedbaccess statements that will drop all GP users in the database */declare MyCursor CURSOR for select 'sp_revokedbaccess ' + '''' + RTRIM(a.name) + '''' from sysusers a where a.name NOT IN ('dbo', 'sys', 'guest', 'INFORMATION_SCHEMA') AND a.name IN (select userid from DYNAMICS..SY01400) order by 1/* Step through each row in the cursor, executing each sp_revokedbaccess statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursor/* Create cursor to hold sp_grantdbaccess statements that will add GP users to the database *//* This will look to the User-Company Access (SY60100) table in the DYNAMICS database */declare MyCursor CURSOR for select 'sp_grantdbaccess ' + '''' + RTRIM(a.USERID) + '''' from DYNAMICS..SY60100 a, DYNAMICS..SY01500 b where a.CMPANYID = b.CMPANYID AND RTRIM(b.INTERID) = @New_InterID AND a.USERID not in ('DYNSA', 'sa') order by 1/* Step through each row in the cursor, executing each sp_grantdbaccess statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursor/* Create cursor to hold sp_addrolemember statements that will add current users to the DYNGRP database role*//* This will look to the User-Company Access (SY60100) table in the DYNAMICS database */declare MyCursor CURSOR for select 'sp_addrolemember ' + '''' + 'DYNGRP' + '''' + ', ' + '''' + RTRIM(a.USERID) + '''' from DYNAMICS..SY60100 a, DYNAMICS..SY01500 b where a.CMPANYID = b.CMPANYID AND RTRIM(b.INTERID) = @New_InterID AND a.USERID not in ('DYNSA', 'sa') order by 1/* Step through each row in the cursor, executing each sp_addrolemember statement in turn */set nocount onopen MyCursor fetch next from MyCursor into @MyStatement while (@@FETCH_STATUS <> -1) begin EXEC (@MyStatement) FETCH NEXT FROM MyCursor INTO @MyStatement endDEALLOCATE MyCursorGO/* Run the Grant.SQL script against the restored database */declare @cStatement varchar(255)declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects where (type = 'U' or type = 'V') and uid = 1set nocount onOPEN G_cursorFETCH NEXT FROM G_cursor INTO @cStatementWHILE (@@FETCH_STATUS <> -1)begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatementendDEALLOCATE G_cursordeclare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects where type = 'P'set nocount onOPEN G_cursorFETCH NEXT FROM G_cursor INTO @cStatementWHILE (@@FETCH_STATUS <> -1)begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatementendDEALLOCATE G_cursorGO