Friday, January 30, 2015

Moving the tempdb database

You don't actually have to move the tempdb files.  You just need to change the path to a new location and then restart SQL Server.  The files will automatically be rebuilt in th new location and you can delete the old files.

1. Verify logical name and physical path of the tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

2. Change the tempdb paths
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')
GO

3. Verify the paths
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

4. Check for any open connections before we bounce SQL
SELECT DB_NAME(dbid) as DBName,
       COUNT(dbid) as NumberOfConnections,
       loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

5. Bounce SQL

6. Use windows explorer to delete the old tempdb files


at No comments:

Moving a SQL User Database

To move a SQL user database, follow these steps:

1. Take db offline
ALTER DATABASE database_name SET OFFLINE;
GO

2. Use windows explorer to move the data and log files to the new location while the database is offline

3. Change the paths to the files
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
GO

4. Bring db back online
ALTER DATABASE database_name SET ONLINE;
GO

5. Verify that the database is online
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'');
at No comments:

Creating a SQL job to automatically refresh a test database

SQL Job to refresh TEST from PRODUCTION

Last Updated: 2018.11.12

 I like to include each of these steps as a separate job step.  If you are doing this for a GP company database and you have MobileTech installed, you should also restore the test_RESCOXRM database from the production one at the same time.  This should save you having to totally re-sync the middle tier.
 STEP 1 – Restore Test Company from a production backup
Note: I need to add the code to check for and also copy the backup of the DB_RESCOXRM database.

--change PROD to your production database name
--GET BACKUP FILENAME FOR MOST RECENT BACKUP
DECLARE @FileName nvarchar(128)

SELECT TOP 1 @FileName=physical_device_name
FROM msdb.dbo.backupset b
INNER JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = 'PROD'
and type = 'D'
and name is not null
ORDER BY backup_finish_date DESC

--change TEST to your TEST database name
ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--change TEST to your TEST database name
--change the string after the MOVE commands to the PROD database logical file names
--change the paths to the TEST database file paths
RESTORE DATABASE [TEST] FROM DISK = @FileName WITH FILE = 1,
MOVE N'GPSPRODDat.mdf' TO N'D:\TEST.mdf',
MOVE N'GPSPRODLog.ldf' TO N'D:\TEST_1.ldf',
NOUNLOAD, REPLACE, STATS = 100

--change TEST to your TEST database name
--change the NAME value to the logical filename from PROD
--change the NEWNAME value to the logical filename for TEST
ALTER DATABASE TEST MODIFY FILE (NAME=N'GPSBECDat.mdf', NEWNAME=N'TEST.mdf')
GO
ALTER DATABASE TEST MODIFY FILE (NAME=N'GPSBECLog.ldf', NEWNAME=N'TEST_1.ldf')
GO

--change TEST to your TEST database name
ALTER DATABASE TEST SET MULTI_USER
GO

Step 2 - drop triggers used by ActivReporter
Note: only if applicable
-----------------------------------------------------------------
--- DGP - Drop Database Triggers used by ActivReporter
---
--- History:
--- DRC 01/10/2018 Added new triggers from GPAddTriggers.sql
---
--- Tables:
--- GL00100 D/I/U Account Master
--- GL40200 D/I/U Segment Description Master
--- GL20000 I Year-to-Date Transaction Open
--- GL10000 D/I/U Transaction Work
--- GL10001 D/I/U Transacion Amounts Work
--- GL00102 D/I/U Account Categories
--- SY00300 I Account Format Setup
--- SY40100 D/I/U Fiscal Calendar Periods
--- SY40101 U Period Header
----------------------------------------------------------------

---  Drop Triggers on table: GL00100  ---
IF OBJECT_ID('AS_GL00100_D') IS NOT NULL
DROP TRIGGER [AS_GL00100_D]

IF OBJECT_ID('AS_GL00100_I') IS NOT NULL
DROP TRIGGER [AS_GL00100_I]

IF OBJECT_ID('AS_GL00100_U') IS NOT NULL
DROP TRIGGER [AS_GL00100_U];

---  Drop Triggers on table: GL40200  ---
IF OBJECT_ID('AS_GL40200_D') IS NOT NULL
DROP TRIGGER [AS_GL40200_D];

IF OBJECT_ID('AS_GL40200_I') IS NOT NULL
DROP TRIGGER [AS_GL40200_I];

IF OBJECT_ID('AS_GL40200_U') IS NOT NULL
DROP TRIGGER [AS_GL40200_U];

---  Drop the Trigger on table: GL20000  ---
IF OBJECT_ID('AS_GL20000_I') IS NOT NULL
DROP TRIGGER [AS_GL20000_I];

---  Drop Triggers on table: GL10000  ---
IF OBJECT_ID('AS_GL10000_D') IS NOT NULL
DROP TRIGGER [AS_GL10000_D];

IF OBJECT_ID('AS_GL10000_I') IS NOT NULL
DROP TRIGGER [AS_GL10000_I];

IF OBJECT_ID('AS_GL10000_U') IS NOT NULL
DROP TRIGGER [AS_GL10000_U];

---  Drop Triggers on table: GL10001  ---
IF OBJECT_ID('AS_GL10001_D') IS NOT NULL
DROP TRIGGER [AS_GL10001_D];

IF OBJECT_ID('AS_GL10001_I') IS NOT NULL
DROP TRIGGER [AS_GL10001_I];

IF OBJECT_ID('AS_GL10001_U') IS NOT NULL
DROP TRIGGER [AS_GL10001_U];

--- Drop Triggers on table: GL00102 ---
IF OBJECT_ID('AS_GL00102_D') IS NOT NULL
DROP TRIGGER [AS_GL00102_D];

IF OBJECT_ID('AS_GL00102_I') IS NOT NULL
DROP TRIGGER [AS_GL00102_I];

IF OBJECT_ID('AS_GL00102_U') IS NOT NULL
DROP TRIGGER [AS_GL00102_U];

--- Drop the Trigger on table: SY00300 ---
IF OBJECT_ID('AS_SY00300_I') IS NOT NULL
DROP TRIGGER [AS_SY00300_I];

-- DROP triggers on table: GL00200
IF OBJECT_ID('AS_GL00200_D') IS NOT NULL
DROP TRIGGER [AS_GL00200_D];

IF OBJECT_ID('AS_GL00200_I') IS NOT NULL
DROP TRIGGER [AS_GL00200_I];

IF OBJECT_ID('AS_GL00200_U') IS NOT NULL
DROP TRIGGER [AS_GL00200_U];


-- DROP triggers on table: GL00201
IF OBJECT_ID('AS_GL00201_D') IS NOT NULL
DROP TRIGGER [AS_GL00201_D];

IF OBJECT_ID('AS_GL00201_I') IS NOT NULL
DROP TRIGGER [AS_GL00201_I];

IF OBJECT_ID('AS_GL00201_U') IS NOT NULL
DROP TRIGGER [AS_GL00201_U];


--DROP trigers on table: SY40100
IF (OBJECT_ID('AS_SY40100_D')) IS NOT NULL
DROP TRIGGER AS_SY40100_D;

IF (OBJECT_ID('AS_SY40100_I')) IS NOT NULL
DROP TRIGGER AS_SY40100_I;

IF (OBJECT_ID('AS_SY40100_U')) IS NOT NULL
DROP TRIGGER AS_SY40100_U;


--DROP trigers on table: SY40101
IF (OBJECT_ID('AS_SY40101_U')) IS NOT NULL
DROP TRIGGER AS_SY40101_U;
GO

--DROP triggers on <DYNAMICS>..MC00100
DECLARE @system_database_name SYSNAME;
IF OBJECT_ID('SY00100') IS NOT NULL
SELECT @system_database_name = RTRIM(DBNAME) FROM SY00100;
IF @system_database_name IS NULL
SET @system_database_name = 'DYNAMICS';

IF DB_ID(@system_database_name) IS NOT NULL
BEGIN
DECLARE @SQL_STATEMENT NVARCHAR(MAX);
IF (OBJECT_ID('['+@system_database_name+']..AS_MC00100_'+CONVERT(NVARCHAR,DB_ID())+'_U')) IS NOT NULL
BEGIN
SET @SQL_STATEMENT = 'USE ['+@system_database_name+'];DROP TRIGGER AS_MC00100_'+CONVERT(NVARCHAR,DB_ID())+'_U;';
EXEC(@SQL_STATEMENT);
END;
END;
GO
 STEP 3 – Update Company ID values

/******************************************************************************/
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values FROM DYNAMICS.dbo.SY01500 table */
/******************************************************************************/
USE TEST
GO

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100')
begin
--handles 2013 and forward
declare @Statement varchar(8000)
select @Statement = '
declare @cStatement varchar(8000)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a
CROSS JOIN '+rtrim(DBNAME)+'.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
and b.INTERID = db_name()
and COLUMN_DEFAULT is not null
and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) != ''SY00100-DBNAME''
order by a.TABLE_NAME

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
close G_cursor
DEALLOCATE G_cursor
set nocount off'
from dbo.SY00100
exec (@Statement)
end
else
begin
--handles legacy
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
from INFORMATION_SCHEMA.COLUMNS a
cross join DYNAMICS.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
and b.INTERID = db_name()
and COLUMN_DEFAULT is not null
order by a.TABLE_NAME
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
close G_cursor
DEALLOCATE G_cursor
set nocount off
end
GO

STEP 4 – Set DB Owner

USE TEST
GO

sp_changedbowner 'DYNSA'
GO


STEP 5 – Update WSRepts report path values
USE TEST
GO

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE name = 'WSRepts')
update dbo.WSRepts
set ReportLocation = REPLACE(ReportLocation, '/PROD/', '/TEST/')
where ReportLocation like '%PROD%'
go

STEP 6 – Update Mobile TEC report path values

USE TEST
GO

IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE name = 'WSMobileTechReportOption')
update dbo.WSMobileTechReportOption
set ReportPathName = REPLACE(ReportPathName, '/PROD/', '/TEST/')
where ReportPathName like '%PROD%'
go

If you have MobileTech installed and you have ever tried to restore a PROD database over test, you have found out that MobileTech won't work right on the test database.  That is because there are some stored procs in the company database that have the resco database name hard coded.  I have added a step here that should handle updating these procs without you having to find and replace all those values.  This should prevent the need to re-run MT Admin create objects to fix them.

 Step 7 - Correct Resco Procs
DECLARE @objname NVARCHAR(max),
@cmd NVARCHAR(max),
@newDB VARCHAR(max),
@oldDB VARCHAR(max),
@beg INT,
@len INT

SELECT @newDB = convert(VARCHAR(max), rtrim(lower(db_name())) + '_RESCOXRM')
DECLARE @SQL VARCHAR(MAX),
@STAT INT

SELECT @STAT = CURSOR_STATUS('global', 'c_MTProcs')

IF @STAT >= - 1
BEGIN
IF @STAT > - 1
BEGIN
SELECT @SQL = 'CLOSE c_MTProcs'
EXEC (@SQL)
END

SELECT @SQL = 'DEALLOCATE c_MTProcs'
EXEC (@SQL)
END

DECLARE c_MTProcs CURSOR
FOR
SELECT obs.[name],
mods.[definition]
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
WHERE obs.is_ms_shipped = 0
AND obs.[type_desc] IN ('SQL_STORED_PROCEDURE')
AND obs.[name] LIKE 'WSMobileLoad%'

OPEN c_MTProcs

FETCH NEXT
FROM c_MTProcs
INTO @objname,
@cmd

WHILE @@fetch_status != - 1
BEGIN
IF (PATINDEX('%FROM __RESCOXRM%', @cmd) = 0)
BEGIN
IF (PATINDEX('%FROM ___RESCOXRM%', @cmd) = 0)
BEGIN
IF (PATINDEX('%FROM ____RESCOXRM%', @cmd) = 0)
BEGIN
IF (PATINDEX('%FROM _____RESCOXRM%', @cmd) = 0)
BEGIN
IF (PATINDEX('%FROM ______RESCOXRM%', @cmd) = 0)
BEGIN
FETCH NEXT
FROM c_MTProcs
INTO @objname,
@cmd
CONTINUE;
END
ELSE
BEGIN
SELECT @beg = PATINDEX('%FROM ______RESCOXRM%', @cmd) + 5
END
END
ELSE
BEGIN
SELECT @beg = PATINDEX('%FROM _____RESCOXRM%', @cmd) + 5
END
END
ELSE
BEGIN
SELECT @beg = PATINDEX('%FROM ____RESCOXRM%', @cmd) + 5
END
END
ELSE
BEGIN
SELECT @beg = PATINDEX('%FROM ___RESCOXRM%', @cmd) + 5
END
END
ELSE
BEGIN
SELECT @beg = PATINDEX('%FROM __RESCOXRM%', @cmd) + 5
END

SELECT @len = PATINDEX('%RESCOXRM%', @cmd) + 8 - @beg
SELECT @oldDB = substring(@cmd, @beg, @len)
SELECT @cmd = REPLACE(@cmd, convert(VARCHAR(max), N'CREATE PROCEDURE'), convert(VARCHAR(max), N'ALTER PROCEDURE'))
SELECT @cmd = REPLACE(@cmd, @oldDB, @newDB)
SELECT @cmd = REPLACE(@cmd, convert(VARCHAR(max), N'..'), convert(VARCHAR(max), N'.dbo.'))
EXEC (@cmd)

FETCH NEXT
FROM c_MTProcs
INTO @objname,
@cmd
END

SELECT @STAT = CURSOR_STATUS('global', 'c_MTProcs')
IF @STAT >= - 1
BEGIN
IF @STAT> - 1
BEGIN
SELECT @SQL = 'CLOSE c_MTProcs'
EXEC (@SQL)
END

SELECT @SQL = 'DEALLOCATE c_MTProcs'
EXEC (@SQL)

END

go

In signature 2018 there are also 3 triggers that have the DB hardcoded.
The triggers are:
zDT_SVRMA300U
zDT_SVRMA102U
zDT_SVRMA101U

There is no reason I can tell for the DB to be hard coded, so I suggest just scripting these out on the PROD database, removing the DB references and reinstalling them.
Step 8  - fix branch key issue in MT

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FTBranchLookup]'))
               DROP VIEW [dbo].[FTBranchLookup];
GO

CREATE VIEW [dbo].[FTBranchLookup]
AS
               SELECT 
                              Wennsoft_Branch=RTRIM(sv00015.Branch_Name)
               FROM sv00015
               JOIN sv00196 ON sv00196.Record = 1 AND Service_Option_Box_5 = 1
GO

GRANT SELECT ON [dbo].[FTBranchLookup] TO [DYNGRP]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FTTechnicianBranch]'))
               DROP VIEW [dbo].[FTTechnicianBranch];
GO

CREATE VIEW [dbo].[FTTechnicianBranch]
AS
               SELECT 
                              TechnicianId=RTRIM(SV00114.Technician), 
                              Affiliate=RTRIM(SV00114.Wennsoft_Affiliate),
                              Region=RTRIM(SV00114.Wennsoft_Region),
                              Branch=RTRIM(sv00015.Branch_Name),
                              HomeBranch=SV00114.Home_Branch,
                              IsExceptionBranch=SV00114.WSReserved_CB1
               FROM SV00114
               JOIN SV00115 ON SV00115.Technician = SV00114.Technician
               JOIN sv00015 ON sv00015.Wennsoft_Affiliate = SV00114.Wennsoft_Affiliate AND sv00015.Wennsoft_Region = SV00114.Wennsoft_Region AND sv00015.Wennsoft_Branch = SV00114.Wennsoft_Branch
               JOIN sv00196 ON sv00196.Record = 1 AND Service_Option_Box_5 = 1
               WHERE SV00114.Technician <> ''
                 AND SV00115.Vendor_Switch = 0
GO

GRANT SELECT ON [dbo].[FTTechnicianBranch] TO [DYNGRP]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/* Delete If Already Exists */
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SV00114_INSERT_SYNC]'))
               DROP TRIGGER [dbo].[SV00114_INSERT_SYNC];
GO

/* Create Trigger */
CREATE TRIGGER [dbo].[SV00114_INSERT_SYNC] ON [dbo].[SV00114] FOR INSERT AS

               /* Initialize Variables */
               DECLARE @EntityName VARCHAR(50) = 'TechnicianBranch';
               DECLARE @ActionType VARCHAR(10) = 'Create';
               DECLARE @Priority int = 1;
               DECLARE @IntegrationUser VARCHAR(50);
               DECLARE @GPKeys TABLE([GP1] VARCHAR(100), [GP2] VARCHAR(100), [GP3] VARCHAR(100), [GP4] VARCHAR(100), [GP5] VARCHAR(100), [GP6] VARCHAR(100), [GP7] VARCHAR(100), [GP8] VARCHAR(100), [GP9] VARCHAR(100), [GP10] VARCHAR(100), [GP11] VARCHAR(100), [GP12] VARCHAR(100));

               /* Populate the Variable Tables */
               EXEC @IntegrationUser = wsGetMobileIntegrationUser;
               INSERT INTO @GPKeys 
               SELECT
                              ISNULL(RTRIM(i.Technician), -1) AS [GP1],
                              ISNULL(RTRIM(i.Wennsoft_Affiliate), -1) AS [GP2],
                              ISNULL(RTRIM(i.Wennsoft_Region), -1) AS [GP3],
                              ISNULL(RTRIM(sv00015.Branch_Name), -1) AS [GP4],
                              '' AS [GP5],
                              '' AS [GP6],
                              '' AS [GP7],
                              '' AS [GP8],
                              '' AS [GP9],
                              '' AS [GP10],
                              '' AS [GP11],
                              '' AS [GP12] 
               FROM [INSERTED] i
               JOIN sv00015 ON sv00015.Wennsoft_Affiliate = i.Wennsoft_Affiliate AND sv00015.Wennsoft_Region = i.Wennsoft_Region AND sv00015.Wennsoft_Branch = i.Wennsoft_Branch;

               /* Check the filter conditions */
               IF (
                              ((SELECT SYSTEM_USER) <> (SELECT [dbo].[wsGetMobileIntegrationUser]()))
               )
               BEGIN
                              INSERT INTO [dbo].[WSMobileTechSync] ([EntityName], [Action], [InProcess], [ModifiedOn], [Priority], [GPKeySegment1], [GPKeySegment2], [GPKeySegment3], [GPKeySegment4], [GPKeySegment5], [GPKeySegment6], [GPKeySegment7], [GPKeySegment8], [GPKeySegment9], [GPKeySegment10], [GPKeySegment11], [GPKeySegment12])
                              SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12
                              FROM @GPKeys;
               END

               SET @EntityName = 'BranchUser';
               BEGIN
                              INSERT INTO [dbo].[WSMobileTechSync] ([EntityName], [Action], [InProcess], [ModifiedOn], [Priority], [GPKeySegment1], [GPKeySegment2], [GPKeySegment3], [GPKeySegment4], [GPKeySegment5], [GPKeySegment6], [GPKeySegment7], [GPKeySegment8], [GPKeySegment9], [GPKeySegment10], [GPKeySegment11], [GPKeySegment12])
                              SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12
                              FROM @GPKeys;
               END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/* Delete If Already Exists */
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SV00114_UPDATE_SYNC]'))
               DROP TRIGGER [dbo].[SV00114_UPDATE_SYNC];
GO

/* Create Trigger */
CREATE TRIGGER [dbo].[SV00114_UPDATE_SYNC] ON [dbo].[SV00114] FOR UPDATE AS

               /* Initialize Variables */
               DECLARE @EntityName VARCHAR(50) = 'TechnicianBranch';
               DECLARE @ActionType VARCHAR(10) = 'Update';
               DECLARE @Priority int = 1;
               DECLARE @IntegrationUser VARCHAR(50);
               DECLARE @GPKeys TABLE([GP1] VARCHAR(100), [GP2] VARCHAR(100), [GP3] VARCHAR(100), [GP4] VARCHAR(100), [GP5] VARCHAR(100), [GP6] VARCHAR(100), [GP7] VARCHAR(100), [GP8] VARCHAR(100), [GP9] VARCHAR(100), [GP10] VARCHAR(100), [GP11] VARCHAR(100), [GP12] VARCHAR(100));

               /* Populate the Variable Tables */
               EXEC @IntegrationUser = wsGetMobileIntegrationUser;
               INSERT INTO @GPKeys 
               SELECT
                              ISNULL(RTRIM(d.Technician), -1) AS [GP1],
                              ISNULL(RTRIM(d.Wennsoft_Affiliate), -1) AS [GP2],
                              ISNULL(RTRIM(d.Wennsoft_Region), -1) AS [GP3],
                              ISNULL(RTRIM(sv00015.Branch_Name), -1) AS [GP4],
                              '' AS [GP5],
                              '' AS [GP6],
                              '' AS [GP7],
                              '' AS [GP8],
                              '' AS [GP9],
                              '' AS [GP10],
                              '' AS [GP11],
                              '' AS [GP12] 
               FROM [DELETED] d
               JOIN sv00015 ON sv00015.Wennsoft_Affiliate = d.Wennsoft_Affiliate AND sv00015.Wennsoft_Region = d.Wennsoft_Region AND sv00015.Wennsoft_Branch = d.Wennsoft_Branch;

               /* Check the filter conditions */
               IF (
                              ((SELECT SYSTEM_USER) <> (SELECT [dbo].[wsGetMobileIntegrationUser]()))
               )
               BEGIN
                              INSERT INTO [dbo].[WSMobileTechSync] ([EntityName], [Action], [InProcess], [ModifiedOn], [Priority], [GPKeySegment1], [GPKeySegment2], [GPKeySegment3], [GPKeySegment4], [GPKeySegment5], [GPKeySegment6], [GPKeySegment7], [GPKeySegment8], [GPKeySegment9], [GPKeySegment10], [GPKeySegment11], [GPKeySegment12])
                              SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12
                              FROM @GPKeys;
               END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/* Delete if Already Exists */
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SV00114_DELETE_SYNC]'))
               DROP TRIGGER [dbo].[SV00114_DELETE_SYNC];
GO

/* Create Trigger */
CREATE TRIGGER [dbo].[SV00114_DELETE_SYNC] ON [dbo].[SV00114] FOR DELETE AS

               /* Initialize Variables */
               DECLARE @EntityName VARCHAR(50) = 'TechnicianBranch';
               DECLARE @ActionType VARCHAR(10) = 'Delete';
               DECLARE @Priority int = 1;
               DECLARE @IntegrationUser VARCHAR(50);
               DECLARE @GPKeys TABLE([GP1] VARCHAR(100), [GP2] VARCHAR(100), [GP3] VARCHAR(100), [GP4] VARCHAR(100), [GP5] VARCHAR(100), [GP6] VARCHAR(100), [GP7] VARCHAR(100), [GP8] VARCHAR(100), [GP9] VARCHAR(100), [GP10] VARCHAR(100), [GP11] VARCHAR(100), [GP12] VARCHAR(100));

               /* Populate the Variable Tables */
               EXEC @IntegrationUser = wsGetMobileIntegrationUser;
               INSERT INTO @GPKeys 
               SELECT
                              ISNULL(RTRIM(d.Technician), -1) AS [GP1],
                              ISNULL(RTRIM(d.Wennsoft_Affiliate), -1) AS [GP2],
                              ISNULL(RTRIM(d.Wennsoft_Region), -1) AS [GP3],
                              ISNULL(RTRIM(sv00015.Branch_Name), -1) AS [GP4],
                              '' AS [GP5],
                              '' AS [GP6],
                              '' AS [GP7],
                              '' AS [GP8],
                              '' AS [GP9],
                              '' AS [GP10],
                              '' AS [GP11],
                              '' AS [GP12] 
               FROM [DELETED] d
               JOIN sv00015 ON sv00015.Wennsoft_Affiliate = d.Wennsoft_Affiliate AND sv00015.Wennsoft_Region = d.Wennsoft_Region AND sv00015.Wennsoft_Branch = d.Wennsoft_Branch;

               /* Check the filter conditions */
               IF (
                              ((SELECT SYSTEM_USER) <> (SELECT [dbo].[wsGetMobileIntegrationUser]()))
               )
               BEGIN
                              /* Assumed Precondition is that this entry exists in the middle tier db */
                              INSERT INTO [dbo].[WSMobileTechSync] ([EntityName], [Action], [InProcess], [ModifiedOn], [Priority], [GPKeySegment1], [GPKeySegment2], [GPKeySegment3], [GPKeySegment4], [GPKeySegment5], [GPKeySegment6], [GPKeySegment7], [GPKeySegment8], [GPKeySegment9], [GPKeySegment10], [GPKeySegment11], [GPKeySegment12])
                              SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12
                              FROM @GPKeys;
               END

               SET @EntityName = 'BranchUser';
               BEGIN
                              INSERT INTO [dbo].[WSMobileTechSync] ([EntityName], [Action], [InProcess], [ModifiedOn], [Priority], [GPKeySegment1], [GPKeySegment2], [GPKeySegment3], [GPKeySegment4], [GPKeySegment5], [GPKeySegment6], [GPKeySegment7], [GPKeySegment8], [GPKeySegment9], [GPKeySegment10], [GPKeySegment11], [GPKeySegment12])
                              SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12
                              FROM @GPKeys;
               END
GO


DECLARE @dbname NVARCHAR(50) = 'pctpr_RESCOXRM'
DECLARE @sql NVARCHAR(1025)

SET @sql = CONCAT('BEGIN DELETE FROM ', @dbname, '..branch DELETE FROM ', @dbname, '..technicianbranch DELETE FROM ', @dbname, '..branch_user END EXEC WSMobileLoadBranch EXEC WSMobileLoadTechnicianBranch EXEC WSMobileLoadBranchUser')
EXEC sp_executesql @sql




STEP 9 – Analytical Accounting

NOTE: This step is only required when the customer is using Analytical Accounting

USE TEST
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aagUpdateAAG00102]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[aagUpdateAAG00102]
GO

create procedure aagUpdateAAG00102
as
/*******************************************************************************************
NAME: AA_Update_AAG00102.sql

DESCRIPTION:
Updates the AAG00102 with the highest values from the Analytical Accounting
transaction tables, and tree, dimension, and code setup tables.

INSTRUCTIONS:
1. Make a full backup of the DYNAMICS database.
2. Run the script against the company database.

GP VERSIONS: 10.0, 2010, 2013

REVISION HISTORY:
Date Who Comments
------------- -------------- --------------------------------------------------------
04/12/2005 v-villaw Created initial script
02/28/2011 dspecht Added additional tables
09/10/2012 kenhub Added consideration for the SY00800 table
*********************************************************************************************/
begin
set nocount on
declare @CMPANYID smallint,
@maxValue int
select @maxValue = 0
select distinct @CMPANYID = CMPANYID from DYNAMICS.dbo.SY01500 where INTERID = DB_NAME()
select @maxValue = isnull(max(aaGLHdrID), 0) from dbo.AAG30000
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 30000 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaSubLedgerHdrID), 0) from dbo.AAG20000
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 20000 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaGLWorkHdrID), 0) from dbo.AAG10000
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 10000 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaTreeID), 0) from dbo.AAG00600
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 600 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaTrxDimID), 0) from dbo.AAG00400
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 400 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaTrxDimCodeID), 0) from dbo.AAG00401
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 401 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaDateID), 0) from dbo.AAG00500
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 500 and CMPANYID = @CMPANYID
select @maxValue = (select isnull(MAX(MaxValue), 0) as MaxOverAll from
(select MAX(AAG00800.aaAliasID) as MaxValue from dbo.AAG00800
Union ALL
select isnull(MAX(AAG00201.aaAcctclassID), 0) as MaxValue from dbo.AAG00201) AS Subquery)
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 201 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaBudgetTreeID), 0) from dbo.AAG00900
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 900 and CMPANYID = @CMPANYID
select @maxValue = isnull(max(aaBudgetID), 0) from dbo.AAG00903
if @maxValue > 0
update DYNAMICS.dbo.AAG00102 set aaRowID = @maxValue where aaTableID = 903 and CMPANYID = @CMPANYID
set nocount off

end
GO

exec aagUpdateAAG00102

drop procedure aagUpdateAAG00102

GO



step 10 - handle quadra
Note: only if applicable

--run on company database
exec sp_change_users_login 'AUTO_FIX', 'QuadraWebUser'
go

--QuadraGPRole
--Run on Quadra Database

if not exists(select * from sys.sysusers where [name]='QuadraWebUser_Test')
begin
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test';
EXEC sp_addrolemember 'db_owner', 'QuadraWebUser_Test';

EXEC sp_grantdbaccess 'READONLY_QUADRA_TEST', 'READONLY_QUADRA_TEST';
EXEC sp_addrolemember 'db_datareader', 'READONLY_QUADRA_TEST'

create role [QuadraGPReadOnlyRole];
grant select on sys.objects TO [QuadraGPReadOnlyRole]
EXEC sp_addrolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST';

end

go

--run on company database

-- Run this file against the GP database to grant the QuadraWebUser_Test and READONLY_QUADRA_TEST users access to GP and DYNAMICS.
-- The SQL will switch to the DYNAMICS database near the end.
-- The SQL assumes:
-- QuadraWebUser_Test already exists on the database server.
-- General SELECT, INSERT, UPDATE, DELETE, ALTER access is revoked.
-- db_owner access is removed.
-- READONLY_QUADRA_TEST already exists on the database server.
-- General SELECT access is revoked.
-- db_reader access is removed.


--use DYNAMICS;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--use DEV;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--EXEC sp_droprolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST';
--GO
--drop role [QuadraGPReadOnlyRole];
--GO

--
--Create roles.
IF NOT EXISTS(select 1 from sys.database_principals where name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole.';
create role [QuadraGPRole];
END
GO

IF NOT EXISTS (select 1 from sys.database_principals where name='QuadraGPReadOnlyRole' and Type = 'R')
BEGIN
print 'Create QuadraGPReadOnlyRole.';
create role [QuadraGPReadOnlyRole];
END
GO

-- Grant database access to users and assign roles.
IF ( not EXISTS ( select [name] from sysusers where [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test GP database access.';
EXEC sp_GRANTdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
End
GO

print ''

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test in GP database.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;
print 'Assign QuadraWebUser_Test to QuadraGPRole in GP database.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print ''

IF ( not EXISTS ( select [name] from sysusers where [name]='READONLY_QUADRA_TEST' and issqluser = 1))
BEGIN
print 'Grant READONLY_QUADRA_TEST GP database access.';
EXEC sp_GRANTdbaccess 'READONLY_QUADRA_TEST', 'READONLY_QUADRA_TEST'
End
GO

print 'Revoke general SELECT access to READONLY_QUADRA_TEST in GP database.'
REVOKE SELECT TO READONLY_QUADRA_TEST;

print 'Assign READONLY_QUADRA_TEST to QuadraGPReadOnlyRole in GP database.'
EXEC sp_addrolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST'
EXEC sp_droprolemember 'db_datareader', 'READONLY_QUADRA_TEST';
print '';

--**********************************************************************************************************************************************
--********************************** QuadraGPRole access *******************************************************************************
--**********************************************************************************************************************************************
print 'Grant table access to QuadraGPRole.';
print ''

GRANT SELECT on sys.objects TO [QuadraGPRole];
GRANT SELECT on sys.triggers TO [QuadraGPRole];

IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'EXT00101'))
BEGIN
GRANT SELECT, INSERT, DELETE, UPDATE ON EXT00101 TO [QuadraGPRole];
GRANT SELECT, INSERT ON EXT00200 TO [QuadraGPRole];
GRANT SELECT, INSERT ON EXT00201 TO [QuadraGPRole];
GRANT SELECT, INSERT ON EXT00203 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE ON EXT01100 TO [QuadraGPRole];
GRANT SELECT, INSERT ON EXT01101 TO [QuadraGPRole];
GRANT SELECT ON EXT20100 TO [QuadraGPRole];
GRANT SELECT ON EXT20010 TO [QuadraGPRole];
GRANT SELECT ON EXT40302 TO [QuadraGPRole];
GRANT SELECT ON EXT40300 TO [QuadraGPRole];
END

GRANT SELECT ON GL00100 TO [QuadraGPRole];
GRANT SELECT ON GL40200 TO [QuadraGPRole];

-- ALTER is needed on JC and POP tables due to needing to disable and re-enable MobileTech triggers when inserting on PO's, SOPs and Job details
GRANT SELECT, INSERT, DELETE, UPDATE, ALTER ON JC00102 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE, UPDATE ON JC00106 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON JC00107 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON JC00114 TO [QuadraGPRole];
GRANT SELECT ON JC00401 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE, UPDATE ON JC00501 TO [QuadraGPRole];
GRANT SELECT, DELETE, UPDATE, ALTER ON JC00701 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE, UPDATE ON JC00901 TO [QuadraGPRole];
GRANT SELECT ON JC01001 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON JC01002 TO [QuadraGPRole];
GRANT SELECT ON JC01701 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, ALTER ON JC10211 TO [QuadraGPRole];
GRANT SELECT ON JC20001 TO [QuadraGPRole];
GRANT SELECT, INSERT ON JC20002 TO [QuadraGPRole];
GRANT SELECT ON JC30001 TO [QuadraGPRole];
GRANT SELECT ON JC30101 TO [QuadraGPRole];
GRANT SELECT ON JC30107 TO [QuadraGPRole];
GRANT SELECT ON JC30301 TO [QuadraGPRole];
GRANT SELECT, INSERT ON JC30701 TO [QuadraGPRole];

GRANT SELECT ON JC40107 TO [QuadraGPRole];
GRANT SELECT ON JC40103 TO [QuadraGPRole];
GRANT SELECT ON JC40202 TO [QuadraGPRole];
GRANT SELECT ON JC40208 TO [QuadraGPRole];
GRANT SELECT ON JC40301 TO [QuadraGPRole];
GRANT SELECT ON JC40305 TO [QuadraGPRole];
GRANT SELECT ON JC41101 TO [QuadraGPRole];
GRANT SELECT ON JCFiscalPeriodRange TO [QuadraGPRole];

GRANT SELECT, INSERT ON IV00101 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00102 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON IV00103 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00105 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00106 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00107 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00108 TO [QuadraGPRole];
GRANT SELECT, INSERT ON IV00115 TO [QuadraGPRole];
GRANT SELECT ON IV40201 TO [QuadraGPRole];
GRANT SELECT ON IV40400 TO [QuadraGPRole];
GRANT SELECT ON IV40700 TO [QuadraGPRole];

GRANT SELECT ON MC40000 TO [QuadraGPRole];
GRANT SELECT ON PM00200 TO [QuadraGPRole];

GRANT SELECT, INSERT, ALTER ON POP00101 TO [QuadraGPRole];
GRANT SELECT, UPDATE, ALTER ON POP10100 TO [QuadraGPRole];
GRANT SELECT, UPDATE, ALTER ON POP10110 TO [QuadraGPRole];
GRANT SELECT ON POP10160 TO [QuadraGPRole];
GRANT SELECT ON POP10500 TO [QuadraGPRole];
GRANT SELECT ON POP30100 TO [QuadraGPRole];
GRANT SELECT ON POP30110 TO [QuadraGPRole];
GRANT SELECT ON POP40100 TO [QuadraGPRole];

GRANT SELECT ON RM00101 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON RM00102 TO [QuadraGPRole];
GRANT SELECT ON RM00301 TO [QuadraGPRole];

GRANT SELECT ON SOP10100 TO [QuadraGPRole];
GRANT SELECT, UPDATE, DELETE ON SOP10200 TO [QuadraGPRole];
GRANT SELECT ON SOP30300 TO [QuadraGPRole];
GRANT SELECT ON SOP30200 TO [QuadraGPRole];
GRANT SELECT ON SOP40100 TO [QuadraGPRole];
GRANT SELECT ON SOP40200 TO [QuadraGPRole];

GRANT SELECT ON SV00015 TO [QuadraGPRole];
GRANT SELECT ON SV00054 TO [QuadraGPRole];
GRANT SELECT ON SV00055 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00100 TO [QuadraGPRole];
GRANT SELECT ON SV00115 TO [QuadraGPRole];
GRANT SELECT ON SV00196 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00200 TO [QuadraGPRole];
GRANT SELECT ON SV00400 TO [QuadraGPRole];
GRANT SELECT ON SV00500 TO [QuadraGPRole];
GRANT SELECT ON SV00501 TO [QuadraGPRole];
GRANT SELECT ON SV00572 TO [QuadraGPRole];
GRANT SELECT ON SVC00203 TO [QuadraGPRole];
GRANT SELECT ON SVC00710 TO [QuadraGPRole];
GRANT SELECT ON SVC00980 TO [QuadraGPRole];

GRANT SELECT ON SY01200 TO [QuadraGPRole];
GRANT SELECT ON SY03000 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SY03900 TO [QuadraGPRole];

GRANT SELECT ON TX00101 TO [QuadraGPRole];
GRANT SELECT ON TX00102 TO [QuadraGPRole];
GRANT SELECT ON TX00201 TO [QuadraGPRole];

-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPRole];
GRANT SELECT ON UPR40300 TO [QuadraGPRole];
GRANT SELECT ON UPR40301 TO [QuadraGPRole];
GRANT SELECT ON UPR40600 TO [QuadraGPRole];

GRANT SELECT, INSERT ON WSS10100 TO [QuadraGPRole];
GRANT SELECT, INSERT ON WS10101 TO [QuadraGPRole];
GRANT SELECT, INSERT, ALTER ON WSS10200 TO [QuadraGPRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPRole];

IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_CntrctNums'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_CntrctNums TO [QuadraGPRole];
End
GO
IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_VP_HDR'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_VP_HDR TO [QuadraGPRole];
End
GO
IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_VP_QTYS'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_VP_QTYS TO [QuadraGPRole];
End
GO
GRANT select on BDO_Message TO [QuadraGPRole];

print 'Grant QuadraGPRole access to GP stored procedures.';
GRANT EXECUTE on sopGetNumber to [QuadraGPRole];
GRANT EXECUTE on sopGetIDNumber to [QuadraGPRole];
GRANT EXECUTE on ivNumber_Inc_Dec to [QuadraGPRole];
GRANT EXECUTE on taSopHdrIvcInsert to [QuadraGPRole];
GRANT EXECUTE on taSopLineIvcInsert to [QuadraGPRole];
GRANT EXECUTE on wsiJCJobMaster to [QuadraGPRole];
GRANT EXECUTE on wsiJCJobDetailMSTR to [QuadraGPRole];
GRANT EXECUTE on wsiJCChangeOrdersMSTR to [QuadraGPRole];
GRANT EXECUTE on wsiJCChangeOrdersDetailMSTR to [QuadraGPRole];
GRANT EXECUTE on JCCopyJobArchitects to [QuadraGPRole];
GRANT EXECUTE on JCCopyJobAddresses to [QuadraGPRole];
GRANT EXECUTE on JCCopyJobUserDefined to [QuadraGPRole];
GRANT EXECUTE on JCUpdateCalcPercentCompleteInJobMstr to [QuadraGPRole];
GRANT EXECUTE on SVC_POP_Make_PO to [QuadraGPRole];
GRANT EXECUTE on taPopIvcTaxInsert to [QuadraGPRole];
GRANT EXECUTE on JC_CreateJobContractAmountAuditEntry to [QuadraGPRole];
GRANT EXECUTE on WSXML_JC00401 to [QuadraGPRole];
GRANT EXECUTE on dbo.taSopLineIvcTaxInsert to [QuadraGPRole] 
GRANT EXECUTE on dbo.wsiSMSLocation to [QuadraGPRole] 
GRANT EXECUTE on WSErrorState to [QuadraGPRole] 

print '';
print 'Grant QuadraGPRole access to Quadra stored procedures.';

IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_wennsoftSOPUpdateCommittedCosts' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_wennsoftSOPUpdateCommittedCosts].'
GRANT EXECUTE on [ERTH_wennsoftSOPUpdateCommittedCosts] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerExists' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerExists].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerExists] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerAdd].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerAddressExists' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerAddressExists].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerAddressExists] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerAddressAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerAddressAdd].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerAddressAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordEquipmentExists' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordEquipmentExists].'
GRANT EXECUTE on [ERTH_serviceRecordEquipmentExists] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordEquipmentAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordEquipmentAdd].'
GRANT EXECUTE on [ERTH_serviceRecordEquipmentAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerAddressQuickSearchExists' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerAddressQuickSearchExists].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerAddressQuickSearchExists] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordCustomerAddressQuickSearchAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordCustomerAddressQuickSearchAdd].'
GRANT EXECUTE on [ERTH_serviceRecordCustomerAddressQuickSearchAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordEquipmentQuickSearchExists' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordEquipmentQuickSearchExists].'
GRANT EXECUTE on [ERTH_serviceRecordEquipmentQuickSearchExists] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_serviceRecordEquipmentQuickSearchAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_serviceRecordEquipmentQuickSearchAdd].'
GRANT EXECUTE on [ERTH_serviceRecordEquipmentQuickSearchAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_poWennsoftValuesUpdate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_poWennsoftValuesUpdate].'
GRANT EXECUTE on [ERTH_poWennsoftValuesUpdate] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_vendorsReadFiltered' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_vendorsReadFiltered].'
GRANT EXECUTE on [ERTH_vENDorsReadFiltered] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_itemDefaultVendor' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_itemDefaultVendor].'
GRANT EXECUTE on [ERTH_itemDefaultVENDor] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_itemVendorRead' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_itemVendorRead].'
GRANT EXECUTE on [ERTH_itemVENDorRead] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_poRead' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_poRead].'
GRANT EXECUTE on [ERTH_poRead] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_costCodeFormatRead' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_costCodeFormatRead].'
GRANT EXECUTE on [ERTH_costCodeFormatRead] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_poTypesForDropShipUpdate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_poTypesForDropShipUpdate].'
GRANT EXECUTE on [ERTH_poTypesForDropShipUpdate] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_UpdateJobContractAmountAuditEntry' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_UpdateJobContractAmountAuditEntry].'
GRANT EXECUTE on [ERTH_UpdateJobContractAmountAuditEntry] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_jobAddressInsert' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_jobAddressInsert].'
GRANT EXECUTE on [ERTH_jobAddressInsert] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_getAllJobTypes' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_getAllJobTypes].'
GRANT EXECUTE on [ERTH_getAllJobTypes] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_wennsoftSOPCreateDocument' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_wennsoftSOPCreateDocument].'
GRANT EXECUTE on [ERTH_wennsoftSOPCreateDocument] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_wennsoftSOPCreateLineItem' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_wennsoftSOPCreateLineItem].'
GRANT EXECUTE on [ERTH_wennsoftSOPCreateLineItem] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_wennsoftSOPUpdateCommittedCosts' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_wennsoftSOPUpdateCommittedCosts].'
GRANT EXECUTE on [ERTH_wennsoftSOPUpdateCommittedCosts] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_vendorItemReadByVendorItemId' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_vendorItemReadByVendorItemId].'
GRANT EXECUTE on [ERTH_vENDorItemReadByVENDorItemId] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_inventoryVendorAdd' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_inventoryVendorAdd].'
GRANT EXECUTE on [ERTH_inventoryVENDorAdd] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_inventoryVendorCostUpdate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_inventoryVendorCostUpdate].'
GRANT EXECUTE on [ERTH_inventoryVENDorCostUpdate] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_getGPInventoryInformation' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_getGPInventoryInformation].'
GRANT EXECUTE on [ERTH_getGPInventoryInformation] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_ItemReadByVendorId' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_ItemReadByVendorId].'
GRANT EXECUTE on [ERTH_ItemReadByVENDorId] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_customerReadPaging' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_customerReadPaging].'
GRANT EXECUTE on [ERTH_customerReadPaging] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_customerRead' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_customerRead].'
GRANT EXECUTE on [ERTH_customerRead] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_jobAddressInsert' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_jobAddressInsert].'
GRANT EXECUTE on [ERTH_jobAddressInsert] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_inventoryQuantitiesReadBySite' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_inventoryQuantitiesReadBySite].'
GRANT EXECUTE on [ERTH_inventoryQuantitiesReadBySite] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_UpdateJobContractAmountAuditEntry' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_UpdateJobContractAmountAuditEntry].'
GRANT EXECUTE on [ERTH_UpdateJobContractAmountAuditEntry] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_itemVendorPricing' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_itemVendorPricing].'
GRANT EXECUTE on [ERTH_itemVENDorPricing] to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'ERTH_addPOLine' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_addPOLine].'
GRANT EXECUTE on ERTH_addPOLine to [QuadraGPRole];
END
GO


IF EXISTS (select name from sys.objects where name='BDO_IntegrationObjectMaster' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationObjectMaster.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationObjectMaster to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='BDO_IntegrationSchemaSummary' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationSchemaSummary.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationSchemaSummary to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='BDO_IntegrationSchemaDetail' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationSchemaDetail.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationSchemaDetail to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='BDO_IntegrationRuleMaster' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationRuleMaster.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationRuleMaster to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='BDO_IntegrationRuleApply' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationRuleApply.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationRuleApply to [QuadraGPRole];
END
GO

IF EXISTS (select name from sys.objects where name='BDO_IntegrationLogSummary' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationLogSummary.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationLogSummary to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='BDO_IntegrationLogDetail' and type='U')
BEGIN
print 'Granting SELECT,INSERT,UPDATE,DELETE on [QuadraGPRole] on BDO_IntegrationLogDetail.'
GRANT SELECT,INSERT,UPDATE,DELETE on dbo.BDO_IntegrationLogDetail to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_InsertSummary' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_InsertSummary.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_InsertSummary to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_InsertDetail' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_InsertDetail.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_InsertDetail to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_DeleteIntegrationByID' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_DeleteIntegrationByID.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_DeleteIntegrationByID to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_DeleteIntegrationByDateRange' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_DeleteIntegrationByDateRange.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_DeleteIntegrationByDateRange to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_UpdateCounts' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_UpdateCounts.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_UpdateCounts to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_UpdateArchive' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_UpdateArchive.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_UpdateArchive to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_ReportSummary' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_ReportSummary.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_ReportSummary to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='sp_BDO_IntegrationLog_ReportDetail' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_IntegrationLog_ReportDetail.'
GRANT EXECUTE on dbo.sp_BDO_IntegrationLog_ReportDetail to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='fn_BDO_FormatMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_FormatMessage.'
GRANT EXECUTE on dbo.fn_BDO_FormatMessage to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='fn_BDO_ParseMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ParseMessage.'
GRANT EXECUTE on dbo.fn_BDO_ParseMessage to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='fn_BDO_IsUserInRoleWithTask' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_IsUserInRoleWithTask.'
GRANT EXECUTE on dbo.fn_BDO_IsUserInRoleWithTask to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='fn_BDO_CostCodeKey' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_CostCodeKey.'
GRANT EXECUTE on dbo.fn_BDO_CostCodeKey to [QuadraGPRole];
END

IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'fn_BDO_ParseList' and type = 'TF')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ParseList.'
GRANT SELECT on dbo.fn_BDO_ParseList to [QuadraGPRole];
END
GO

IF EXISTS (select name from sys.objects where name='fn_BDO_ExtendedFormatMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ExtendedFormatMessage.'
GRANT EXECUTE on dbo.fn_BDO_ExtENDedFormatMessage to [QuadraGPRole];
END

IF EXISTS (select name from sys.objects where name='fn_BDO_ExtendedParseMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ExtendedParseMessage.'
GRANT EXECUTE on dbo.fn_BDO_ExtENDedParseMessage to [QuadraGPRole];
END
GO

IF EXISTS (select name from sys.objects where name='fn_BDO_ExtendedParseMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ExtendedParseMessage.'
GRANT EXECUTE on dbo.fn_BDO_ExtENDedParseMessage to [QuadraGPRole];
END
GO
IF EXISTS (select name from sys.objects where name='fn_BDO_ExtendedParseMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_ExtendedParseMessage.'
GRANT EXECUTE on dbo.fn_BDO_ExtENDedParseMessage to [QuadraGPRole];
END
GO

IF EXISTS (select name from sys.objects where name='fn_BDO_GetMessage' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_GetMessage.'
GRANT EXECUTE on fn_BDO_GetMessage to [QuadraGPRole];
END
GO
IF EXISTS (select name from sys.objects where name='fn_BDO_GetMessageComponent' and type='FN')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on fn_BDO_GetMessageComponent.'
GRANT EXECUTE on dbo.fn_BDO_GetMessageComponent to [QuadraGPRole];
END
GO

IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'sp_BDO_QuadraConnector_CreateWennsoftSOPQuote' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_CreateWennsoftSOPQuote.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_CreateWennsoftSOPQuote to [QuadraGPRole]; 
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'sp_BDO_QuadraConnector_CreateEstimateRevision' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_CreateEstimateRevision.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_CreateEstimateRevision to [QuadraGPRole]; 
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'fn_BDO_QuadraConnector_JobCostDetailSOPDetailTable' and type = 'IF')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on [QuadraGPRole] on fn_BDO_QuadraConnector_JobCostDetailSOPDetailTable.'
GRANT SELECT on dbo.fn_BDO_QuadraConnector_JobCostDetailSOPDetailTable to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'dbo.sp_BDO_GenericConnector_GetSOPNumber' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on dbo.sp_BDO_GenericConnector_GetSOPNumber.'
GRANT EXECUTE on dbo.sp_BDO_GenericConnector_GetSOPNumber to [QuadraGPRole]; 
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'sp_BDO_QuadraConnector_Create_SOP' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_Create_SOP.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_Create_SOP to [QuadraGPRole]; 
END
GO
IF EXISTS(select name from sys.objects where ltrim(rtrim(name)) = 'sp_BDO_QuadraConnector_Create_SOP_Line' and type='P')
BEGIN
print 'Granting execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_Create_SOP_Line.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_Create_SOP_Line to [QuadraGPRole];
END
GO
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractCustomer' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractCustomer.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractCustomer to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractProject' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractProject.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractProject to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractDivision' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractDivision.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractDivision to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractProjectManager' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractProjectManager.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractProjectManager to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractJobType' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractJobType.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractJobType to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractCostCode' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractCostCode.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractCostCode to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractJob' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractJob.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractJob to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractContractType' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractContractType.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractContractType to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractEstimator' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractEstimator.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractEstimator to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractItemDefaultCostCode' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractItemDefaultCostCode.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractItemDefaultCostCode to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_ExtractJobAutoComplete' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_ExtractJobAutoComplete.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_ExtractJobAutoComplete to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_Create_AP' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_Create_AP.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_Create_AP to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = '[dbo].[sp_BDO_QuadraConnector_Create_NS]' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [dbo].[sp_BDO_QuadraConnector_Create_NS].'
GRANT EXECUTE on dbo.[dbo].[sp_BDO_QuadraConnector_Create_NS] to [QuadraGPRole] 
END
IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = '[dbo].[VW_BDO_QUADRA_APPROVAL_AMOUNTS]')
BEGIN
print 'Grant select,insert,update,delete,references,view definition on [QuadraGPRole] on [dbo].[VW_BDO_QUADRA_APPROVAL_AMOUNTS].'
GRANT select,insert,update,delete,references,view definition on dbo.[dbo].[VW_BDO_QUADRA_APPROVAL_AMOUNTS] to [QuadraGPRole] 
END
IF EXISTS(select name from sys.objects where LTRIM(rtrim(name)) = 'sp_BDO_QuadraConnector_UpdateSetting' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on sp_BDO_QuadraConnector_UpdateSetting.'
GRANT EXECUTE on dbo.sp_BDO_QuadraConnector_UpdateSetting to [QuadraGPRole] 
END


IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_JobCostActualCost_GroupByCostCode')
BEGIN
print 'Grant select on [QuadraGPRole] on ERTHv_JobCostActualCost_GroupByCostCode.'
GRANT select on dbo.ERTHv_JobCostActualCost_GroupByCostCode to [QuadraGPRole] 
END

IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'Grant select on [QuadraGPRole] on ERTHv_Customer.'
GRANT select on dbo.ERTHv_Customer to [QuadraGPRole] 
END

IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'Grant select on [QuadraGPRole] on ERTHv_CustomerAddressDetail.'
GRANT select  on dbo.ERTHv_CustomerAddressDetail to [QuadraGPRole] 
END

IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'aagGetCompanyStatus' and type='P')
BEGIN
GRANT EXECUTE ON aagGetCompanyStatus TO [QuadraGPRole];
END
GO

print '';


--**********************************************************************************************************************************************
--********************************** QuadraGPReadOnlyRole access *******************************************************************************
--**********************************************************************************************************************************************

print 'Grant table access to QuadraGPReadOnlyRole.';

GRANT SELECT on sys.objects TO [QuadraGPReadOnlyRole];
GRANT SELECT on sys.triggers TO [QuadraGPReadOnlyRole];

IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'EXT00101'))
BEGIN
GRANT SELECT ON EXT00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT00200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT00201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT00203 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT01100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT01101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT20100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT20010 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT40302 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON EXT40300 TO [QuadraGPReadOnlyRole];
END

GRANT SELECT ON GL00100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON GL40200 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON JC00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00106 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00107 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00114 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00401 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00501 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00701 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC00901 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC01001 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC01002 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC01701 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC10211 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC20001 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC20002 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC30001 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC30101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC30107 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC30301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC30701 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON JC40107 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40103 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40202 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40208 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40305 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC41101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JCFiscalPeriodRange TO [QuadraGPReadOnlyRole];

GRANT SELECT ON IV00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00103 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00105 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00106 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00107 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00108 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00115 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40700 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON MC40000 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON PM00200 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON POP00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP10100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP10110 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP10160 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP10500 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP30100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP30110 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON POP40100 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON RM00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00301 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SOP10100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SOP10200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SOP30300 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SOP30200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SOP40100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SOP40200 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV00015 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00054 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00055 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00115 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00196 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00500 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00501 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01150 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00572 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SVC00203 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SVC00710 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SVC00980 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SY01100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY01200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY03000 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY03900 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON TX00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON TX00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON TX00201 TO [QuadraGPReadOnlyRole];

-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPReadOnlyRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40300 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40600 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON WSS10100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WS10101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSS10200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPReadOnlyRole];

IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_CntrctNums'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_CntrctNums TO [QuadraGPReadOnlyRole];
End
GO
IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_VP_HDR'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_VP_HDR TO [QuadraGPReadOnlyRole];
End
GO
IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tcsPOPTB00044_VP_QTYS'))
BEGIN
GRANT SELECT ON tcsPOPTB00044_VP_QTYS TO [QuadraGPReadOnlyRole];
End
GO
GRANT SELECT on BDO_Message TO [QuadraGPReadOnlyRole];

IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_JobCostActualCost_GroupByCostCode')
BEGIN
print 'Grant select on [QuadraGPReadOnlyRole] on ERTHv_JobCostActualCost_GroupByCostCode.'
GRANT SELECT on dbo.ERTHv_JobCostActualCost_GroupByCostCode to [QuadraGPReadOnlyRole] 
END

IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'Grant select on [QuadraGPReadOnlyRole] on ERTHv_Customer.'
GRANT SELECT on dbo.ERTHv_Customer to [QuadraGPReadOnlyRole] 
END

IF EXISTS(select * from sys.views where LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'Grant select on [QuadraGPReadOnlyRole] on ERTHv_CustomerAddressDetail.'
GRANT SELECT  on dbo.ERTHv_CustomerAddressDetail to [QuadraGPReadOnlyRole] 
END
print '';

--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above this point. ***********************************************
--**********************************************************************************************************************************************
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'SMARTCONNECT')
BEGIN
EXEC sp_executesql 
N'
USE [SMARTCONNECT];

IF NOT EXISTS (select 1 from sys.database_principals where name=''QuadraGPRole'' and Type = ''R'')
BEGIN
print ''Create QuadraGPRole in SMARTCONNECT.'';
create role [QuadraGPRole];
END

IF ( not EXISTS ( select [name] from sysusers where [name]=''QuadraWebUser_Test'' and issqluser = 1))
BEGIN
print ''Grant QuadraWebUser_Test SMARTCONNECT database access.'';
EXEC sp_grantdbaccess ''QuadraWebUser_Test'', ''QuadraWebUser_Test''
END

print ''Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.''
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test

print ''Assign QuadraWebUser_Test to QuadraGPRole in SMARTCONNECT database.''
EXEC sp_addrolemember ''QuadraGPRole'', ''QuadraWebUser_Test''
EXEC sp_droprolemember ''db_owner'', ''QuadraWebUser_Test''
'
END
GO

--******************************************************************************************************************************************************************
--********************************** All GP database role access and SmartConnect access should be set above this point. *******************************************
--******************************************************************************************************************************************************************
USE [DYNAMICS];
GO

if not exists (select 1 from sys.database_principals where name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole in DYNAMICS database.';
create role [QuadraGPRole];
END
GO

IF ( not EXISTS ( select [name] from sysusers where [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test DYNAMICS database access.';
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
End
GO

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;

print 'Assign QuadraWebUser_Test to QuadraGPRole in DYNAMICS database.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print '';
print 'Grant table access to QuadraGPRole in DYNAMICS database.';
GRANT SELECT ON DYNAMICS.dbo.MC40200 TO [QuadraGPRole];
GRANT SELECT ON dynamics.dbo.SY00800 TO [QuadraGPRole];
GRANT SELECT ON dynamics.dbo.sy01400 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON dynamics.dbo.sy01500 TO [QuadraGPRole];
GRANT SELECT ON dynamics.dbo.SY05501 TO [QuadraGPRole];
GRANT SELECT ON dynamics.dbo.sy60100 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.sy09100 TO [QuadraGPRole];
GRANT SELECT ON dynamics.dbo.sy10500 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.taErrorCode TO [QuadraGPRole];
GRANT SELECT on DYNAMICS.sys.objects TO [QuadraGPRole];
GRANT EXECUTE on tasmGetNextNoteIndex TO [QuadraGPRole];
GRANT EXECUTE on smGetConstantInt to [QuadraGPRole];
GRANT EXECUTE on taDEXLOCKS to [QuadraGPRole];
GRANT EXECUTE on [smGetNextNoteIndex]  to [QuadraGPRole]
GRANT SELECT ON DYNAMICS.dbo.MC60100 TO [QuadraGPRole];
print '';
--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above the DYNAMICS access. **********************************
--**********************************************************************************************************************************************


-- Run this file against the GP database to grant the QuadraWebUser_Test and READONLY_QUADRA_TEST users access to GP and DYNAMICS.
-- The SQL will switch to the DYNAMICS database near the END.
-- The SQL assumes:
-- QuadraWebUser_Test already EXISTS ON the database server.
-- General SELECT, INSERT, UPDATE, DELETE, ALTER access is revoked.
-- db_owner access is removed.
-- READONLY_QUADRA_TEST already EXISTS ON the database server.
-- General SELECT access is revoked.
-- db_reader access is removed.


--use DYNAMICS;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--use DEV;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--EXEC sp_droprolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST';
--GO
--drop role [QuadraGPReadOnlyRole];
--GO

--
--Create roles.
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole.';
create role [QuadraGPRole];
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPReadOnlyRole' and Type = 'R')
BEGIN
print 'Create QuadraGPReadOnlyRole.';
create role [QuadraGPReadOnlyRole];
END
GO

-- Grant database access to users and assign roles.
IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test GP database access.';
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
END
GO

print ''

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;
print 'Assign QuadraWebUser_Test to QuadraGPRole.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print ''

IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='READONLY_QUADRA_TEST' and issqluser = 1))
BEGIN
print 'Grant READONLY_QUADRA_TEST GP database access.';
EXEC sp_grantdbaccess 'READONLY_QUADRA_TEST', 'READONLY_QUADRA_TEST'
END
GO

print 'Revoke general SELECT access to READONLY_QUADRA_TEST.'
REVOKE SELECT TO READONLY_QUADRA_TEST;

print 'Assign READONLY_QUADRA_TEST to QuadraGPReadOnlyRole.'
EXEC sp_addrolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST'
EXEC sp_droprolemember 'db_datareader', 'READONLY_QUADRA_TEST';
print '';

--**********************************************************************************************************************************************
--********************************** QuadraGPRole access *******************************************************************************
--**********************************************************************************************************************************************
print 'Grant table access to QuadraGPRole in GP database.';
print ''

GRANT SELECT ON sys.objects TO [QuadraGPRole];

-- need these for Quadra Setup
GRANT SELECT ON JC40202 TO [QuadraGPRole];
GRANT SELECT ON JC40208 TO [QuadraGPRole];
GRANT SELECT ON JC40301 TO [QuadraGPRole];

GRANT SELECT ON IV00101 TO [QuadraGPRole];
GRANT SELECT ON IV00103 TO [QuadraGPRole];
GRANT SELECT ON IV40201 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON IV40400 TO [QuadraGPRole];
GRANT SELECT ON IV40700 TO [QuadraGPRole];
GRANT SELECT ON MC40000 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON RM00101 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON RM00102 TO [QuadraGPRole];
GRANT SELECT ON RM00301 TO [QuadraGPRole];

-- creating indexes ON tables requires ALTER permissions
GRANT SELECT ON SV_00415 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SVAUTONM TO [QuadraGPRole];

GRANT SELECT, INSERT, UPDATE, DELETE ON SV000122 TO [QuadraGPRole];
GRANT SELECT ON SV000123 TO [QuadraGPRole];
GRANT SELECT ON SV00015 TO [QuadraGPRole];
GRANT SELECT ON SV00045 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00054 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00055 TO [QuadraGPRole];
GRANT SELECT ON SV00077 TO [QuadraGPRole];
GRANT SELECT, INSERT , UPDATE, DELETE ON SV000805 TO [QuadraGPRole];

GRANT SELECT, INSERT, UPDATE ON SV00100 TO [QuadraGPRole];
GRANT SELECT ON SV00115 TO [QuadraGPRole];
GRANT SELECT ON SV00143 TO [QuadraGPRole];
GRANT SELECT ON SV00165 TO [QuadraGPRole];
GRANT SELECT ON SV00166 TO [QuadraGPRole];
GRANT SELECT ON SV00196 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON SV00199 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00200 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00205 TO [QuadraGPRole];
GRANT SELECT ON SV00215 TO [QuadraGPRole];
GRANT SELECT ON SV00325 TO [QuadraGPRole];
GRANT SELECT, INSERT, ALTER ON SV00400 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, ALTER ON SV00403 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00405 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00410 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00411 TO [QuadraGPRole];
GRANT SELECT ON SV00420 TO [QuadraGPRole];
GRANT SELECT, UPDATE, INSERT ON SV00500 TO [QuadraGPRole];
GRANT SELECT ON SV00501 TO [QuadraGPRole];
GRANT SELECT, UPDATE, INSERT ON SV00502 TO [QuadraGPRole];
GRANT SELECT, INSERT, ALTER ON SV00503 TO [QuadraGPRole];
GRANT SELECT, DELETE, UPDATE ON SV00510 TO [QuadraGPRole];
GRANT SELECT ON SV00515 TO [QuadraGPRole];
GRANT SELECT ON SV00517 TO [QuadraGPRole];
GRANT SELECT ON SV00520 TO [QuadraGPRole];
GRANT SELECT ON SV00525 TO [QuadraGPRole];
GRANT SELECT ON SV00540 TO [QuadraGPRole];
GRANT SELECT ON SV00541 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00559 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00560 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00565 TO [QuadraGPRole];
GRANT SELECT ON SV00572 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00575 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00581 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00582 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00583 TO [QuadraGPRole];
GRANT SELECT ON SV00584 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00585 TO [QuadraGPRole];
GRANT SELECT, INSERT, ALTER ON SV00586 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00591 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE ON SV00592 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE, ALTER ON SV00593 TO [QuadraGPRole];
GRANT SELECT ON SV00735 TO [QuadraGPRole];
GRANT SELECT ON SV00900 TO [QuadraGPRole];

GRANT SELECT, INSERT, UPDATE ON SV01100 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV01105 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV01150 TO [QuadraGPRole];

GRANT SELECT ON SY01200 TO [QuadraGPRole];
GRANT SELECT ON SY40100 TO [QuadraGPRole];
GRANT SELECT ON TX00101 TO [QuadraGPRole];
-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPRole];
GRANT SELECT ON UPR40300 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON UPR40301 TO [QuadraGPRole];
GRANT SELECT ON UPR40600 TO [QuadraGPRole];
GRANT SELECT ON WSDOCEB TO [QuadraGPRole];
GRANT SELECT ON WSDOCS TO [QuadraGPRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPRole];

print 'Grant QuadraGPRole access to GP stored procedures.';
print ''
GRANT EXECUTE ON wsiSMSEquipment TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSContract TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSLocation TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSCustomer TO [QuadraGPRole];
GRANT EXECUTE ON WSErrorState TO [QuadraGPRole];
GRANT EXECUTE ON ivNumber_Inc_Dec TO [QuadraGPRole]; -- wsiSMSCustomer calls this
GRANT EXECUTE ON SMS_Build_Customer_Quicksearch TO [QuadraGPRole]; -- wsiSMSLocation calls this
GRANT EXECUTE ON taCreateCustomerAddress TO [QuadraGPRole]; -- wsiSMSLocation calls this
GRANT EXECUTE ON SMS_Build_Customer_Quicksearch TO [QuadraGPRole]; -- wsiSMSLocation calls this

IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'aagGetCompanyStatus' and type='P')
BEGIN
GRANT EXECUTE ON aagGetCompanyStatus TO [QuadraGPRole];
END
GO

print '';
print 'Grant QuadraGPRole access to Quadra views.';

IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_EquipmentTaskSummary')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_EquipmentTaskSummary.'
GRANT SELECT ON dbo.ERTHv_EquipmentTaskSummary to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_Customer.'
GRANT SELECT ON dbo.ERTHv_Customer to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_CustomerAddressDetail.'
GRANT SELECT  ON dbo.ERTHv_CustomerAddressDetail to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Contact')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_Contact.'
GRANT SELECT  ON dbo.ERTHv_Contact to [QuadraGPRole] 
END
GO
print '';


--**********************************************************************************************************************************************
--********************************** QuadraGPReadOnlyRole access *******************************************************************************
--**********************************************************************************************************************************************

print 'Grant table access to QuadraGPReadOnlyRole.';
print ''

GRANT SELECT ON sys.objects TO [QuadraGPReadOnlyRole];

GRANT SELECT ON JC40202 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40208 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40301 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON IV00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00103 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40700 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON MC40000 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00301 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV_00415 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SVAUTONM TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV000122 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000123 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00015 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00045 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00054 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00055 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00077 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000805 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV00100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00115 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00143 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00165 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00166 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00196 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00199 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00205 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00215 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00325 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00403 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00405 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00410 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00411 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00420 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00500 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00501 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00502 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00503 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00510 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00515 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00517 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00520 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00525 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00540 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00541 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00559 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00560 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00565 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00572 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00575 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00581 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00582 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00583 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00584 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00585 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00586 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00591 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00592 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00593 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00735 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00900 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV01100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01105 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01150 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SY01200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY40100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON TX00101 TO [QuadraGPReadOnlyRole];
-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPReadOnlyRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40300 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40600 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSDOCEB TO [QuadraGPRole];
GRANT SELECT ON WSDOCS TO [QuadraGPRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPReadOnlyRole];

print '';
print 'Grant QuadraGPReadOnlyRole access to Quadra views.';

IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_EquipmentTaskSummary')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_EquipmentTaskSummary.'
GRANT SELECT ON dbo.ERTHv_EquipmentTaskSummary to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_Customer.'
GRANT SELECT ON dbo.ERTHv_Customer to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_CustomerAddressDetail.'
GRANT SELECT  ON dbo.ERTHv_CustomerAddressDetail to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Contact')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_Contact.'
GRANT SELECT  ON dbo.ERTHv_Contact to [QuadraGPReadOnlyRole] 
END
GO
print '';


--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above this point. *******************************************
--**********************************************************************************************************************************************
use [DYNAMICS];
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole in DYNAMICS.';
create role [QuadraGPRole];
END
GO

IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test DYNAMICS database access.';
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
END
GO

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;

print 'Assign QuadraWebUser_Test to QuadraGPRole.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print '';
print 'Grant table access to QuadraGPRole.';
GRANT SELECT ON DYNAMICS.dbo.MC40200 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY00800 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY01400 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON DYNAMICS.dbo.SY01500 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY05501 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY60100 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY09100 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY10500 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.taErrorCode TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.sys.objects TO [QuadraGPRole];
GRANT EXECUTE ON tasmGetNextNoteIndex TO [QuadraGPRole];
GRANT EXECUTE ON smGetConstantInt TO [QuadraGPRole];
GRANT EXECUTE ON taDEXLOCKS TO [QuadraGPRole];
GRANT EXECUTE on [smGetNextNoteIndex]  to [QuadraGPRole]
print '';
--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above the DYNAMICS access. **********************************
--**********************************************************************************************************************************************


-- Run this file against the GP database to grant the QuadraWebUser_Test and READONLY_QUADRA_TEST users access to GP and DYNAMICS.
-- The SQL will switch to the DYNAMICS database near the END.
-- The SQL assumes:
-- QuadraWebUser_Test already EXISTS ON the database server.
-- General SELECT, INSERT, UPDATE, DELETE, ALTER access is revoked.
-- db_owner access is removed.
-- READONLY_QUADRA_TEST already EXISTS ON the database server.
-- General SELECT access is revoked.
-- db_reader access is removed.


--use DYNAMICS;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--use DEV;
--EXEC sp_droprolemember 'QuadraGPRole', 'QuadraWebUser_Test';
--GO
--drop role [QuadraGPRole];
--GO
--EXEC sp_droprolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST';
--GO
--drop role [QuadraGPReadOnlyRole];
--GO

--
--Create roles.
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole.';
create role [QuadraGPRole];
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPReadOnlyRole' and Type = 'R')
BEGIN
print 'Create QuadraGPReadOnlyRole.';
create role [QuadraGPReadOnlyRole];
END
GO

-- Grant database access to users and assign roles.
IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test GP database access.';
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
END
GO

print ''

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;
print 'Assign QuadraWebUser_Test to QuadraGPRole.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print ''

IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='READONLY_QUADRA_TEST' and issqluser = 1))
BEGIN
print 'Grant READONLY_QUADRA_TEST GP database access.';
EXEC sp_grantdbaccess 'READONLY_QUADRA_TEST', 'READONLY_QUADRA_TEST'
END
GO

print 'Revoke general SELECT access to READONLY_QUADRA_TEST.'
REVOKE SELECT TO READONLY_QUADRA_TEST;

print 'Assign READONLY_QUADRA_TEST to QuadraGPReadOnlyRole.'
EXEC sp_addrolemember 'QuadraGPReadOnlyRole', 'READONLY_QUADRA_TEST'
EXEC sp_droprolemember 'db_datareader', 'READONLY_QUADRA_TEST';
print '';

--**********************************************************************************************************************************************
--********************************** QuadraGPRole access *******************************************************************************
--**********************************************************************************************************************************************
print 'Grant table access to QuadraGPRole in GP database.';
print ''

GRANT SELECT ON sys.objects TO [QuadraGPRole];

GRANT SELECT ON IV00103 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON IV40201 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON IV40700 TO [QuadraGPRole]; -- need these for Quadra Setup

GRANT SELECT ON SV00520 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON SV00525 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON SV00735 TO [QuadraGPRole]; -- need these for Quadra Setup

GRANT SELECT, UPDATE, ALTER ON JC00102 TO [QuadraGPRole]; -- Needed for Appt Type = Job Appointment, job cost API
GRANT INSERT ON JC00107 TO [QuadraGPRole]; -- job cost API
GRANT INSERT ON JC00501 TO [QuadraGPRole];
GRANT SELECT, UPDATE, DELETE, ALTER ON JC00701 TO [QuadraGPRole]; -- Needed for Appt Type = Job Appointment, job cost API
GRANT SELECT, UPDATE ON JC00901 TO [QuadraGPRole]; 
GRANT SELECT ON JC01701 TO [QuadraGPRole]; 
GRANT SELECT ON JC30001 TO [QuadraGPRole];
GRANT SELECT ON JC40202 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON JC40208 TO [QuadraGPRole]; -- need these for Quadra Setup
GRANT SELECT ON JC40301 TO [QuadraGPRole]; -- NEEDED FOR JOB COST API CALLS
GRANT SELECT ON JC40305 TO [QuadraGPRole]; -- NEEDED FOR JOB COST API CALLS
GRANT SELECT ON JC40107 TO [QuadraGPRole]; -- NEEDED FOR JOB COST API CALLS

GRANT SELECT ON IV00101 TO [QuadraGPRole];
GRANT SELECT ON IV00102 TO [QuadraGPRole];
GRANT SELECT ON IV00105 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON IV40400 TO [QuadraGPRole];
GRANT SELECT ON MC40000 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON RM00101 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON RM00102 TO [QuadraGPRole];
GRANT SELECT ON RM00201 TO [QuadraGPRole];
GRANT SELECT ON RM00301 TO [QuadraGPRole];
GRANT SELECT, INSERT ON RM00401 TO [QuadraGPRole]; -- Needed for SMS_Get_New_Service_Call_ID, serviceNumberGroup in (1,2)

GRANT SELECT ON SV_00415 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON SV000062 TO [QuadraGPRole];
GRANT SELECT ON SV000122 TO [QuadraGPRole];
GRANT SELECT ON SV000123 TO [QuadraGPRole];
GRANT SELECT ON SV00015 TO [QuadraGPRole];
GRANT SELECT ON SV00045 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00053 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00054 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00055 TO [QuadraGPRole];
GRANT SELECT ON SV00077 TO [QuadraGPRole];
GRANT SELECT ON SV000789 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00100 TO [QuadraGPRole];
GRANT SELECT ON SV00115 TO [QuadraGPRole];
GRANT SELECT ON SV00118 TO [QuadraGPRole];
GRANT SELECT ON SV00143 TO [QuadraGPRole];
GRANT SELECT ON SV00165 TO [QuadraGPRole];
GRANT SELECT ON SV00166 TO [QuadraGPRole];
GRANT SELECT ON SV00196 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON SV00199 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00200 TO [QuadraGPRole];
GRANT SELECT ON SV00205 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00300 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00301 TO [QuadraGPRole]; -- needed for wsiSMSServiceCall
GRANT SELECT, INSERT, UPDATE ON SV00302 TO [QuadraGPRole]; -- needed for wsiSMSServiceCall
GRANT SELECT ON SV00305 TO [QuadraGPRole]; -- used by wsiSMSAppointment
GRANT SELECT ON SV00310 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00311 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00312 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00317 TO [QuadraGPRole];
GRANT SELECT ON SV00320 TO [QuadraGPRole];
GRANT SELECT ON SV00325 TO [QuadraGPRole];
GRANT SELECT ON SV00326 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00340 TO [QuadraGPRole];  -- needed for wsiSMSServiceCall
GRANT SELECT, INSERT, UPDATE ON SV00400 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00403 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00405 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00410 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00411 TO [QuadraGPRole];
GRANT SELECT ON SV00420 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00500 TO [QuadraGPRole];
GRANT SELECT ON SV00502 TO [QuadraGPRole];
GRANT SELECT ON SV00512 TO [QuadraGPRole];
GRANT SELECT ON SV00517 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE ON SV00519 TO [QuadraGPRole];  -- needed for wsiSMSServiceCall
GRANT SELECT ON SV00540 TO [QuadraGPRole];
GRANT SELECT ON SV00541 TO [QuadraGPRole];
GRANT SELECT ON SV00557 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE ON SV00559 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00560 TO [QuadraGPRole];
GRANT SELECT, INSERT ON SV00564 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00565 TO [QuadraGPRole];
GRANT SELECT ON SV00572 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON SV00591 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE ON SV00592 TO [QuadraGPRole];
GRANT SELECT, INSERT, DELETE ON SV00593 TO [QuadraGPRole];
GRANT SELECT ON SV00736 TO [QuadraGPRole];
GRANT SELECT ON SV00584 TO [QuadraGPRole];
GRANT SELECT ON SV00900 TO [QuadraGPRole];
GRANT SELECT ON SV01100 TO [QuadraGPRole];
GRANT SELECT ON SV01115 TO [QuadraGPRole];
GRANT SELECT ON SV40201 TO [QuadraGPRole];
GRANT SELECT ON SY00100 TO [QuadraGPRole];
GRANT SELECT ON SY01200 TO [QuadraGPRole];

GRANT SELECT ON TX00101 TO [QuadraGPRole];
GRANT SELECT ON TX00102 TO [QuadraGPRole];
GRANT SELECT ON TX00201 TO [QuadraGPRole];
-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPRole];
GRANT SELECT ON UPR40300 TO [QuadraGPRole];
GRANT SELECT, INSERT, UPDATE, DELETE ON UPR40301 TO [QuadraGPRole];
GRANT SELECT ON UPR40600 TO [QuadraGPRole];
GRANT SELECT ON UPR41700 TO [QuadraGPRole];
GRANT SELECT, INSERT ON WSDOCEB TO [QuadraGPRole];
GRANT SELECT, INSERT ON WSDOCS TO [QuadraGPRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPRole];

print 'Grant QuadraGPRole access to GP stored procedures.';
print ''
GRANT EXECUTE ON wsiJCJobMaster TO [QuadraGPRole]; --needed for create job API
GRANT EXECUTE ON wsiSMSEquipment TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSCustomer TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSServiceCall TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSServiceTasksMaster TO [QuadraGPRole];
GRANT EXECUTE ON wsiSMSLocation TO [QuadraGPRole]; -- wsiSMSCustomer calls this
GRANT EXECUTE ON wsiSMSEquipmentContract TO [QuadraGPRole]; -- wsiSMSEquipment calls this if contract # is provided
GRANT EXECUTE ON wsiSMSAppointment TO [QuadraGPRole]; --wsiSMSServiceCall calls this
GRANT EXECUTE ON wsiJCJobDetailMSTR TO [QuadraGPRole]; --needed for create job API

GRANT EXECUTE ON ivNumber_Inc_Dec TO [QuadraGPRole]; -- wsiSMSCustomer calls this
GRANT EXECUTE ON taCreateCustomerAddress TO [QuadraGPRole]; -- wsiSMSLocation calls this
GRANT EXECUTE ON SMS_Build_Customer_Quicksearch TO [QuadraGPRole]; -- wsiSMSLocation calls this
GRANT EXECUTE ON SMS_Get_New_Service_Call_ID TO [QuadraGPRole]; -- wsiSMSServiceCall calls this
GRANT EXECUTE ON SMS_Get_Tech_Shift TO [QuadraGPRole]; -- wsiSMSAppointment calls this
GRANT EXECUTE ON wsConvertTimeToGMT TO [QuadraGPRole]; --wsiSMSServiceCall calls this
GRANT EXECUTE ON WSErrorState TO [QuadraGPRole];

print '';
print 'Grant QuadraGPRole access to Quadra stored procedures.';
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_getNextIdForService' and type='IF')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_getNextIdForService].'
GRANT SELECT ON [ERTH_getNextIdForService] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_CustomerEquipmentCreateUpdate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_CustomerEquipmentCreateUpdate].'
GRANT EXECUTE ON [ERTH_CustomerEquipmentCreateUpdate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_CustomerCreate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_CustomerCreate].'
GRANT EXECUTE ON [ERTH_CustomerCreate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_CustomerAddressCreate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_CustomerAddressCreate].'
GRANT EXECUTE ON [ERTH_CustomerAddressCreate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_ServiceCallCreate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_ServiceCallCreate].'
GRANT EXECUTE ON [ERTH_ServiceCallCreate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_ServiceCallTaskCreate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_ServiceCallTaskCreate].'
GRANT EXECUTE ON [ERTH_ServiceCallTaskCreate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_ServiceCallQuoteCreate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_ServiceCallQuoteCreate].'
GRANT EXECUTE ON [ERTH_ServiceCallQuoteCreate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_FixPKSV00312' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_FixPKSV00312].'
GRANT EXECUTE ON [ERTH_FixPKSV00312] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_ServiceCallQuoteUpdate' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_ServiceCallQuoteUpdate].'
GRANT EXECUTE ON [ERTH_ServiceCallQuoteUpdate] to [QuadraGPRole];
END
GO
IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'ERTH_CreateEquipmentFile' and type='P')
BEGIN
print 'Grant execute permission on [QuadraGPRole] on [ERTH_CreateEquipmentFile].'
GRANT EXECUTE ON [ERTH_CreateEquipmentFile] to [QuadraGPRole];
END
GO

IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'aagGetCompanyStatus' and type='P')
BEGIN
GRANT EXECUTE ON aagGetCompanyStatus TO [QuadraGPRole];
END
GO

print '';
print 'Grant QuadraGPRole access to Quadra views.';

IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_EquipmentTaskSummary')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_EquipmentTaskSummary.'
GRANT SELECT ON dbo.ERTHv_EquipmentTaskSummary to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_Customer.'
GRANT SELECT ON dbo.ERTHv_Customer to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_CustomerAddressDetail.'
GRANT SELECT  ON dbo.ERTHv_CustomerAddressDetail to [QuadraGPRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Contact')
BEGIN
print 'GRANT SELECT ON [QuadraGPRole] ON ERTHv_Contact.'
GRANT SELECT  ON dbo.ERTHv_Contact to [QuadraGPRole] 
END
GO
print '';


--**********************************************************************************************************************************************
--********************************** QuadraGPReadOnlyRole access *******************************************************************************
--**********************************************************************************************************************************************

print 'Grant table access to QuadraGPReadOnlyRole.';
print ''

GRANT SELECT ON sys.objects TO [QuadraGPReadOnlyRole];

GRANT SELECT ON IV00103 TO [QuadraGPReadOnlyRole]; 
GRANT SELECT ON IV40201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40700 TO [QuadraGPReadOnlyRole]; 

GRANT SELECT ON SV00520 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00525 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00735 TO [QuadraGPReadOnlyRole]; 

GRANT SELECT ON JC00102 TO [QuadraGPReadOnlyRole]; 
GRANT SELECT ON JC00701 TO [QuadraGPReadOnlyRole]; 
GRANT SELECT ON JC00901 TO [QuadraGPReadOnlyRole]; 
GRANT SELECT ON JC01701 TO [QuadraGPReadOnlyRole]; 
GRANT SELECT ON JC30001 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40202 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40208 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40305 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON JC40107 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON IV00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV00105 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON IV40400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON MC40000 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON RM00401 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON SV_00415 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000062 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000122 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000123 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00015 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00045 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00053 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00054 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00055 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00077 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV000789 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00115 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00118 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00143 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00165 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00166 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00196 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00199 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00200 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00205 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00300 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00302 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00305 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00310 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00311 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00312 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00317 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00320 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00325 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00326 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00340 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00400 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00403 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00405 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00410 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00411 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00420 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00500 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00502 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00512 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00517 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00519 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00540 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00541 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00557 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00559 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00560 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00564 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00565 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00572 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00591 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00592 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00593 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00736 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00584 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV00900 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01115 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV01150 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SV40201 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY00100 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON SY01200 TO [QuadraGPReadOnlyRole];

GRANT SELECT ON TX00101 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON TX00102 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON TX00201 TO [QuadraGPReadOnlyRole];
-- revoke select for entire UPR00100 table and grant select access to specific columns
REVOKE SELECT on UPR00100 TO [QuadraGPReadOnlyRole]
GRANT SELECT ON UPR00100(EMPLOYID, EMPLCLAS, INACTIVE, LASTNAME, MIDLNAME, FRSTNAME, DEPRTMNT) TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40300 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40301 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR40600 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON UPR41700 TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSDOCEB TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSDOCS TO [QuadraGPReadOnlyRole];
GRANT SELECT ON WSXML_Errors TO [QuadraGPReadOnlyRole];

print '';
print 'Grant QuadraGPReadOnlyRole access to Quadra views.';

IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_EquipmentTaskSummary')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_EquipmentTaskSummary.'
GRANT SELECT ON dbo.ERTHv_EquipmentTaskSummary to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Customer')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_Customer.'
GRANT SELECT ON dbo.ERTHv_Customer to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_CustomerAddressDetail')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_CustomerAddressDetail.'
GRANT SELECT  ON dbo.ERTHv_CustomerAddressDetail to [QuadraGPReadOnlyRole] 
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE LTRIM(rtrim(name)) = 'ERTHv_Contact')
BEGIN
print 'GRANT SELECT ON [QuadraGPReadOnlyRole] ON ERTHv_Contact.'
GRANT SELECT  ON dbo.ERTHv_Contact to [QuadraGPReadOnlyRole] 
END
GO
print '';


--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above this point. ***********************************************
--**********************************************************************************************************************************************
IF EXISTS (SELECT name FROM master.sys.databases WHERE name = N'SMARTCONNECT')
BEGIN
EXEC sp_executesql 
N'
USE [SMARTCONNECT];

IF NOT EXISTS (select 1 from sys.database_principals where name=''QuadraGPRole'' and Type = ''R'')
BEGIN
print ''Create QuadraGPRole in SMARTCONNECT.'';
create role [QuadraGPRole];
END

IF ( not EXISTS ( select [name] from sysusers where [name]=''QuadraWebUser_Test'' and issqluser = 1))
BEGIN
print ''Grant QuadraWebUser_Test SMARTCONNECT database access.'';
EXEC sp_grantdbaccess ''QuadraWebUser_Test'', ''QuadraWebUser_Test''
END

print ''Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.''
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test

print ''Assign QuadraWebUser_Test to QuadraGPRole in SMARTCONNECT database.''
EXEC sp_addrolemember ''QuadraGPRole'', ''QuadraWebUser_Test''
EXEC sp_droprolemember ''db_owner'', ''QuadraWebUser_Test''
'
END
GO

--******************************************************************************************************************************************************************
--********************************** All GP database role access and SmartConnect access should be set above this point. *******************************************
--******************************************************************************************************************************************************************
use [DYNAMICS];
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPRole' and Type = 'R')
BEGIN
print 'Create QuadraGPRole in DYNAMICS.';
create role [QuadraGPRole];
END
GO

IF ( NOT EXISTS ( SELECT [name] FROM sysusers WHERE [name]='QuadraWebUser_Test' and issqluser = 1))
BEGIN
print 'Grant QuadraWebUser_Test DYNAMICS database access.';
EXEC sp_grantdbaccess 'QuadraWebUser_Test', 'QuadraWebUser_Test'
END
GO

print 'Revoke general SELECT, INSERT, UPDATE, DELETE, ALTER access to QuadraWebUser_Test.'
REVOKE SELECT, INSERT, UPDATE, DELETE, ALTER TO QuadraWebUser_Test;

print 'Assign QuadraWebUser_Test to QuadraGPRole.'
EXEC sp_addrolemember 'QuadraGPRole', 'QuadraWebUser_Test'
EXEC sp_droprolemember 'db_owner', 'QuadraWebUser_Test';

print '';
print 'Grant table access to QuadraGPRole.';
GRANT SELECT ON DYNAMICS.dbo.MC40200 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY00800 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY01400 TO [QuadraGPRole];
GRANT SELECT, UPDATE ON DYNAMICS.dbo.SY01500 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY05501 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY60100 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY09100 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.SY10500 TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.dbo.taErrorCode TO [QuadraGPRole];
GRANT SELECT ON DYNAMICS.sys.objects TO [QuadraGPRole];
IF ( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'SVCALLSRC'))
BEGIN
GRANT SELECT ON SVCALLSRC TO [QuadraGPRole]; -- this table is used in wsiSMSServiceCall
END
GRANT SELECT ON dynamics.dbo.MC60100 TO [QuadraGPRole];
GRANT EXECUTE ON tasmGetNextNoteIndex TO [QuadraGPRole];
GRANT EXECUTE ON smGetConstantInt TO [QuadraGPRole];
GRANT EXECUTE ON taDEXLOCKS TO [QuadraGPRole];
GRANT EXECUTE on [smGetNextNoteIndex]  to [QuadraGPRole]
print '';
--**********************************************************************************************************************************************
--********************************** All GP database role access should be set above the DYNAMICS access. **********************************
--**********************************************************************************************************************************************




Step 11 –Change  Recovery Model and Shrink DB
Note: only if applicable

       If the TEST company LOG backups are not scheduled then include step to set the Recovery Mode to SIMPLE
       Add the Shrink Database command when there are space limitations.  Note: adding shrink may be devastating to performance on the TEST database.

ALTER DATABASE [TEST] SET RECOVERY SIMPLE
GO

DBCC SHRINKDATABASE(TEST, TRUNCATEONLY)
GO



at No comments:
Subscribe to: Posts (Atom)

SQL 2022 TSQL snapshot backups!

  SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...