Friday, January 30, 2015

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;

--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;

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

SET QUOTED_IDENTIFIER ON

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

DROP VIEW [dbo].[FTBranchLookup];

CREATE VIEW [dbo].[FTBranchLookup]

SELECT

Wennsoft_Branch=RTRIM(sv00015.Branch_Name)

FROM sv00015

JOIN sv00196 ON sv00196.Record = 1 AND Service_Option_Box_5 = 1

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

DROP VIEW [dbo].[FTTechnicianBranch];

CREATE VIEW [dbo].[FTTechnicianBranch]

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

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

/* 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];

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

SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12

FROM @GPKeys;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

/* 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];

/* 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);

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

SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12

FROM @GPKeys;

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

/* 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];

/* 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);

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

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

SELECT @EntityName, @ActionType, 0, GETDATE(), @Priority, GP1, GP2, GP3, GP4, GP5, GP6, GP7, GP8, GP9, GP10, GP11, GP12

FROM @GPKeys;

END

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'

--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

--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

IF NOT EXISTS (select 1 from sys.database_principals where name='QuadraGPReadOnlyRole' and Type = 'R')

BEGIN

print 'Create QuadraGPReadOnlyRole.';

create role [QuadraGPReadOnlyRole];

END

-- 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--******************************************************************************************************************************************************************

--********************************** All GP database role access and SmartConnect access should be set above this point. *******************************************

--******************************************************************************************************************************************************************

USE [DYNAMICS];

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

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

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

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPReadOnlyRole' and Type = 'R')

BEGIN

print 'Create QuadraGPReadOnlyRole.';

create role [QuadraGPReadOnlyRole];

END

-- 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

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

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

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

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 * 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

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

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

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

print '';

--**********************************************************************************************************************************************

--********************************** All GP database role access should be set above this point. *******************************************

--**********************************************************************************************************************************************

use [DYNAMICS];

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

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

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

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name='QuadraGPReadOnlyRole' and Type = 'R')

BEGIN

print 'Create QuadraGPReadOnlyRole.';

create role [QuadraGPReadOnlyRole];

END

-- 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

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

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

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

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

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

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

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

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

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

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

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

IF EXISTS (SELECT NAME FROM sys.objects where ltrim(rtrim(name)) = 'aagGetCompanyStatus' and type='P')

BEGIN

GRANT EXECUTE ON aagGetCompanyStatus TO [QuadraGPRole];

END

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

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 * 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

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

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

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

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

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

--******************************************************************************************************************************************************************

--********************************** All GP database role access and SmartConnect access should be set above this point. *******************************************

--******************************************************************************************************************************************************************

use [DYNAMICS];

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

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

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

Tuesday, January 27, 2015

Reset GP passwords after copying database

After copying a GP database from one GP instance to another, the user ids typically come along for the ride, but the encrypted passwords do not. Here is a handy script to reset the passwords.

/*
1. Run this script in a SQL Server Management Studio window to create output list of users

2. Copy and paste the output list into a new SQL Server Management Studio window

A. review and remove from the list any user's you do not want to reset

B. execute the script

FYI: list will look like this:

ALTER LOGIN djedziniak ENABLE

ALTER LOGIN djedziniak WITH PASSWORD = ''

ALTER LOGIN rgeorge ENABLE

ALTER LOGIN rgeorge WITH PASSWORD = ''

3. On next Logon to GP users should

A. Enter USERID

B. Click OK (leave password field empty)

C. Click YES to the "Blank passwords are not allowed. Do you want to change your password now?" prompt

D. Enter New Password > Tab > re-enter new password > click OK

E. Logon to GP using the new password

C/O: Ricky George
*/
USE master
GO
SET NOCOUNT ON
DECLARE @USER_Name varchar(30)

DECLARE dbname_cursor CURSOR FOR
SELECT USERID
FROM DYNAMICS.dbo.SY01400
WHERE USERID NOT IN ('LESSONUSER1', 'sa', 'DYNSA', 'LESSONUSER2')
ORDER BY USERID

OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @USER_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER LOGIN '+ RTRIM(@USER_name)+ ' ENABLE'
PRINT 'ALTER LOGIN '+ RTRIM(@USER_name)+ ' WITH PASSWORD = '''''
FETCH NEXT FROM dbname_cursor INTO @USER_name
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

Thursday, January 15, 2015

Creating a good set of SQL maintenance plans

Here is an example of a good set of SQL maintenance plans for a server hosting the following:

- Dynamics GP production and test databases

- SSRS databases

First, the database properties need to be checked.

Determine which databases you will do log backups on. These will need the recovery model set to Full. All other system and user databases needs the recovery model set to Simple to prevent runaway log file growth.

Usually:

DYNAMICS and all production company databases are set to Full.

Mobiletech databases (RESCO databases) should be set to Simple (recommendation from WennSoft)
ReportServer databases are set to Simple

Next, determine which databases need to be backed up and how often

Do not backup tempdb. It gets rebuilt when SQL Server is restarted, so you would never restore it anyway. When I refer to system databases, I am only referring to master, model, and msdb.

Remember to backup ReportServerTempDB. This is not rebuilt like tempdb and you will need it if you ever need to restore ReportServer. You may not necessarily need the contents, but you will need the structure.

A typical schedule would be:

system databases - monthly - these databases rarely change

SSRS and non-production databases - weekly - SSRS databases do not significantly change unless new reports are deployed and test databases are typically more tolerant of data loss in a recovery situation. Much of the time, test databases do not need backups as long as they are regularly refreshed from production so the log file does not grow too large.

DYNAMICS and production databases - nightly - these databases should also be Full recovery model with log backups throughout the day.

Counting the logs, this is 4 schedules, so you will need 4 plans.

Next, set up the plans

monthly - scheduled for 1st Sunday of the month at 10pm

1. Check database integrity task - no point in backing up a corrupt database

- specify all system databases

2. Backup database task

- backup type Full

- specify all system databases

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task

- specify all system databases

- take the defaults

4. Update Statistics Task

- specify all system databases

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 3 months

6. History Cleanup Task

- select all options

- set age to 3 months

weekly - scheduled for Sundays at 11pm

1. Check database integrity task - no point in backing up a corrupt database

- specify all non-system databases (you may be able to omit test databases)

2. Backup database task

- backup type Full

- specify all non-system databases (you may be able to omit test databases)

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task - we will do this for all databases weekly

- specify all non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

- specify all non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 12 weeks

daily - scheduled for Mon thru Sat at 11pm

1. Check database integrity task - no point in backing up a corrupt database

- specify all non-system databases that are set to Full

2. Backup database task

- backup type Differential

- specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to dif

- check verify backup integrity

3. Reorganize Index Task

- specify all non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

- specify all non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to dif

- check delete files based on age

- set age to 2 weeks

log backup - scheduled daily every 4 hours from 8am to 8pm

1. Backup database task

- backup type Transaction Log

- specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to trn

- check verify backup integrity

If a database is set to Full recovery model, you NEED TO setup transaction log backups. If you forget this, your log file will grow until it hits the max size or fills up the drive. If you do not want to do transaction log backups on a database, set it to Simple recovery model.

Manually run the plans in the following order;

monthly

weekly

daily

logs

This is important, not just to test them, but to establish full backups for the differentials to work off of.

Monday, January 12, 2015

Fun with Pivot tables

Update:

Here is a link to a much better explanation of Pivot Tables

https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/

Wow, it has been almost a year since my last post! Well, back to it. This is an update to the original post below. Additional info added to the end:

Original post 2012.04.19:
I continue to get questions on how to generate pivot tables. Although the concept seems complicated, the process can actually be quite simple.

Say I have data that looks like this:

and I want it to look like this:

You can accomplish with using a pivot query.

First, let's look at the SQL that generates the data set you are working with.
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status, --this will be out pivot column
(MODIFDT + Modified_Time) as ModDateTime
FROM
SV30301
WHERE
WS_Appointment_ID > 3000
The first step to pivoting the data is to clearly define the base query (above). Any concatenations or case statements need to be done here. It is important that you identify which column you will pivot on. That is the column you want to group horizontally. In this case, it is the status. You will then need to identify all possible values in this column.
BROADCAST
RECEIVED
ACCEPTED
IN ROUTE
ON SITE
PARTS HOLD
COMPLETE
These values will become your column names.

Next, you should determine what data you want to aggregate by these groupings.  Any aggregate function applies here, so you can use functions like SUM, AVG, MAX, and COUNT. (see full list of aggregate functions) In this case, we want to get the max date for each status.

So we first change our original query to prepare for adding the pivot logic:

SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician
FROM ( SELECT
    WS_Appointment_ID,
    Service_Call_ID,
    Appointment,
    Technician,
    Appointment_Status,
    (MODIFDT + Modified_Time) as ModDateTime
FROM
    SV30301
WHERE
    WS_Appointment_ID > 3000
) AS source

Notice that we did not include the pivot column or aggregate column. Those two columns will before a single column in the next step.

Next, we add the pivot logic:
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
ISNULL(BROADCAST,GETDATE()) AS BROADCAST,
RECEIVED,
ACCEPTED,
[IN ROUTE],
[ON SITE],
[PARTS HOLD],
COMPLETE,
UNASSIGNED
FROM (
SELECT
    WS_Appointment_ID,
    Service_Call_ID,
    Appointment,
    Technician,
    Appointment_Status,
    (MODIFDT + Modified_Time) as ModDateTime
FROM
dbo.SV30301

) AS source
PIVOT(
MAX(ModDateTime)
FOR Appointment_Status
IN (
    [BROADCAST],
    [RECEIVED],
    [ACCEPTED],
    [IN ROUTE],
    [ON SITE],
    [PARTS HOLD],
    [COMPLETE],
[UNASSIGNED]
)
) as pvt

In the pivot code, we first perform the aggregate operation on the aggregate column. We issue the FOR keyword followed by the pivot column and then the list of all possible values in the pivot column. Each value should be surrounded by [] (square brackets). We then list all of those values as column names in the upper select statement. Standard select logic applies here. You can rename the columns at this point or perform other operations on them.
--------------------------------------

Update 20150112:

So the above method is great if you already know the values that are in the columns. But what if you don't know the values and still need to pivot. In that case, you will need to dynamically build the query. That means you will need to use a stored proc instead of a view.

--recall that we are pivoting on the appointment status
--first we need to declare a variable and fill it with the possible values from the table
DECLARE @STATUS VARCHAR(MAX)
SELECT @STATUS=(SELECT DISTINCT '[' + RTRIM(Appointment_Status) + '],' FROM dbo.SV30301 WHERE RTRIM(Appointment_Status)!='' FOR XML PATH(''))
SELECT @STATUS=SUBSTRING(@STATUS,1,LEN(@STATUS)-1)

--now @STATUS will replace the "IN" portion and some of the fields in the select
--we must place the query in a variable as a string and then execute it

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL='SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,' + @STATUS + ' FROM (
SELECT
WS_Appointment_ID,
Service_Call_ID,
Appointment,
Technician,
Appointment_Status,
(MODIFDT + Modified_Time) as ModDateTime
FROM
dbo.SV30301

) AS source
PIVOT(
MAX(ModDateTime)
FOR Appointment_Status
IN (' + @STATUS + ')
) as pvt'

EXEC (@SQL)

--that's it.

Did this help you? If so, please leave a comment!

Mighty Code

Friday, January 30, 2015

Creating a SQL job to automatically refresh a test database

SQL Job to refresh TEST from PRODUCTION

Tuesday, January 27, 2015

Reset GP passwords after copying database

Thursday, January 15, 2015

Creating a good set of SQL maintenance plans

Monday, January 12, 2015

Fun with Pivot tables

SQL 2022 TSQL snapshot backups!

Followers