USE [master]
GO

BACKUP DATABASE [PCC] TO DISK = N'E:\Temp\PCC.BAK'
WITH INIT, COPY_ONLY
GO

RESTORE 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 = 10
GO

USE [TSDEV]
GO

/* Change the DBO to DYNSA */
sp_changedbowner 'sa'
GO
sp_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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE 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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE 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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE 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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE 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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE 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 on
open MyCursor
     fetch next from MyCursor into @MyStatement
     while (@@FETCH_STATUS <> -1)
          begin
               EXEC (@MyStatement)
               FETCH NEXT FROM MyCursor INTO @MyStatement
          end
DEALLOCATE MyCursor
GO

/* 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 = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
     EXEC (@cStatement)
     FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
     where type = 'P' 

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
     EXEC (@cStatement)
     FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

GO