Wednesday, October 7, 2015

script to move a database

This script will move the data and log files for a single database.
You need to set the database name and new paths before running.

--SCRIPT TO MOVE INDIVIDUAL DATABASE FILES

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @DATABASE_NAME='TWO',
@NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'

DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)

SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH

SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ',
FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ',
FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

script to move all DYNAMICS databases

I frequently have to move data and log files after GP setup to get them into the best practices configuration. I decided to write a script to move all the common dynamics related databases to new data and log file locations.
This script assumes the system database is named DYNAMICS.
It then handles DYNAMICS, all company databases, resco, report server, smartconnect, and nodebuilder.

--script to move all DYNAMICS databases
--set the new paths and growth rates before running

DECLARE @DATABASE_NAME VARCHAR(255),
@NEWLOGPATH VARCHAR(255),
@NEWDATAPATH VARCHAR(255),
@DATAGROWTHRATE VARCHAR(20),
@LOGGROWTHRATE VARCHAR(20)

SELECT @NEWLOGPATH='C:\SQL\LOG\',
@NEWDATAPATH='C:\SQL\DATA\',
@DATAGROWTHRATE='1024MB',
@LOGGROWTHRATE='512MB'

DECLARE @SQL VARCHAR(MAX),
@DATAFILELOGICAL VARCHAR(255),
@NEWDATAFILEPATH VARCHAR(255),
@OLDDATAFILEPATH VARCHAR(255),
@LOGFILELOGICAL VARCHAR(255),
@NEWLOGFILEPATH VARCHAR(255),
@OLDLOGFILEPATH VARCHAR(255)

DECLARE C_INTERID CURSOR FOR
SELECT RTRIM(INTERID)
FROM DYNAMICS.dbo.SY01500 D
INNER JOIN sys.sysdatabases S
ON D.INTERID=S.name
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME ='DYNAMICS'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'resco%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'ReportServer%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'SmartConnect%'
UNION
SELECT RTRIM(NAME)
FROM sys.sysdatabases
WHERE NAME LIKE 'NodeBuilder%'

OPEN C_INTERID
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @DATAFILELOGICAL='[' + RTRIM(name) + ']',
@OLDDATAFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='ROWS'

SELECT @LOGFILELOGICAL='[' + RTRIM(name) + ']',
@OLDLOGFILEPATH=RTRIM(physical_name)
FROM sys.master_files
WHERE RTRIM(DB_NAME(database_id))=@DATABASE_NAME
AND type_desc='LOG'

SELECT @NEWDATAFILEPATH=@NEWDATAPATH + reverse(left(reverse(@OLDDATAFILEPATH),charindex('\',reverse(@OLDDATAFILEPATH), 1) - 1))
SELECT @NEWLOGFILEPATH=@NEWLOGPATH + reverse(left(reverse(@OLDLOGFILEPATH),charindex('\',reverse(@OLDLOGFILEPATH), 1) - 1))

--SELECT @DATAFILELOGICAL,@DATAFILEPATH,@LOGFILELOGICAL,@LOGFILEPATH

SET NOCOUNT ON
SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET offline
'
PRINT @SQL
EXEC(@SQL)

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH OR @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC sp_configure ''show advanced options'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
EXEC sp_configure ''xp_cmdshell'', 1;
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
RECONFIGURE;
'
PRINT @SQL
EXEC(@SQL)

END

IF @OLDDATAFILEPATH!=@NEWDATAFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDDATAFILEPATH + '" "' + @NEWDATAFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ', FILENAME = "' + @NEWDATAFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

IF @OLDLOGFILEPATH!=@NEWLOGFILEPATH
BEGIN
SELECT @SQL='
EXEC xp_cmdshell ''MOVE "' + @OLDLOGFILEPATH + '" "' + @NEWLOGFILEPATH + '"'';
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ', FILENAME = "' + @NEWLOGFILEPATH + '")
'
PRINT @SQL
EXEC(@SQL)
END

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
SET online
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @DATAFILELOGICAL + ',
FILEGROWTH = ' + @DATAGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + '
MODIFY FILE (NAME = ' + @LOGFILELOGICAL + ',
FILEGROWTH = ' + @LOGGROWTHRATE + ')
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
ALTER DATABASE ' + @DATABASE_NAME + ' SET AUTO_SHRINK OFF
'
PRINT @SQL
EXEC(@SQL)

SELECT @SQL='
USE master
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(''' + @DATABASE_NAME + ''');
'
PRINT @SQL
EXEC(@SQL)
FETCH NEXT FROM C_INTERID INTO @DATABASE_NAME

END

CLOSE C_INTERID
DEALLOCATE C_INTERID

Wednesday, June 10, 2015

VB script to send an ftp file after exporting the file with smartconnect

Dim currentdate as Date= Date.Now()
Dim DateStamp as String=currentdate.Year.ToString() + currentdate.Month.ToString().PadLeft(2,"0") + currentdate.Day.ToString().PadLeft(2,"0")
Dim TimeStamp as String=currentdate.Hour.ToString().PadLeft(2,"0") + currentdate.Minute.ToString().PadLeft(2,"0") + currentdate.Second.ToString().PadLeft(2,"0")

Const username As String = “username”
Const password As String = “password”
Const localFile As String = “\\localshare\filename.txt"

Dim remoteFile As String = “/ftpfolder/filename_" + DateStamp + ".txt”
Dim archiveFile as String = “\\archiveshare\filename_" + DateStamp + "_" + TimeStamp + ".txt"

Const host As String = “ftp://username:password@ftpipaddress"
Dim operation as string
Dim buffLength As Integer = 2048
Dim buff(buffLength - 1) As Byte
Try
If not File.Exists(localFile) then
return false
end if

Dim URI As String = host & remoteFile

operation="create ftp web request"
Dim ftp As System.Net.FtpWebRequest = CType(System.Net.FtpWebRequest.Create(URI), System.Net.FtpWebRequest)

ftp.Credentials = New System.Net.NetworkCredential(username, password)
ftp.KeepAlive = False
ftp.UseBinary = True
ftp.Timeout = 20000
ftp.Method=System.Net.WebRequestMethods.FTP.UploadFile

operation="getting fileinfo for local file"
Dim _FileInfo As New System.IO.FileInfo(localFile)
ftp.ContentLength = _FileInfo.Length

operation="opening filestream"
Dim _FileStream As System.IO.FileStream = _FileInfo.OpenRead()

operation="getrequeststream"
Dim _Stream As System.IO.Stream = ftp.GetRequestStream()

Dim contentLen As Integer = _FileStream.Read(buff, 0, buffLength)

Do While contentLen <> 0
operation="writing stream"
_Stream.Write(buff, 0, contentLen)
contentLen = _FileStream.Read(buff, 0, buffLength)
Loop

operation="closing"
_Stream.Close()
_Stream.Dispose()
_FileStream.Close()
_FileStream.Dispose()

System.IO.File.Move(localFile,archiveFile )

return true
Catch ex As Exception
'MessageBox.Show("operation=" + operation + " " + ex.ToString(), "Upload Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
return false
End Try

Friday, March 27, 2015

Coalesce in C#

I ran into an interesting issue today with string math, which enlightened me to a shortcut function in C# that works similarly to the T-SQL function COALESCE.

I was appending strings to StringBuilders and adding strings using string math. Something like this:

string header=getheader();
string footer=getfooter();
StringBuilder body=new StringBuilder ();
string value1=getvalue1();
string value2=getvalue1();

body.append(header);
body.append(value1 + value2);
body.append(footer);

return body.ToString();

I expected this to always return a string.
However, if value1 is null, there is no error thrown.
The return value is simply null...even if all the other variables have valid strings.

Normally, I would add a function that accepts a string, tests for null, and returns string.empty, as follows:

private static string TestNull(string value){
if(value==null){
return string.empty;
}
else{
return value;
}
}

But what if I want to make it some other value than string.empty? And what if I need to test that value too? Now it gets a bit messy.

I remembered the COALESCE operator in T-SQL.

COALESCE makes this clean by letting you do things like this:
select COALESCE(field1,field2,@myfield,'')
The above statement will roll through the values from left to right and use the first one that is not null.

I thought, maybe there is a built in function for handling this in C#. Google brought forth the golden egg.

?? (null-coalescing operator)

In C#, this operator basically says if the string on the left is null, use the string on the right. And it can be daisy-chained.

so I can say something like this:
value1=value2 ?? header ?? string.empty;

Happy coalescing!

Friday, February 13, 2015

Conditionally stop a trigger from firing

There are instances when inserting records into a table that you might not want one or more triggers to fire. There is an easy way to control this using Context_Info.

The trick is to add code to the trigger(s) that you want to control.

Context_Info() can be set to a hex value in your procedure and then read from the trigger. It is a sneaky way to pass a parameter to a trigger.

At the beginning of your trigger logic, add the following three lines

IF Context_Info() = 0x20220121 RETURN

Then in your procedure, just before you do your insert, update, or delete, add the following line

SET Context_Info 0x20220121

Note that the value 0x20220121 can be any hex value you choose. However, since it is global, I prefer to use a date for the numbers to avoid stepping on it with a later addition.

Also, initially, Context_Info() will return null until it has been set.

Remember to reset it after the insert, update, or delete

SET Context_Info 0x00000000

Tuesday, February 10, 2015

The report server cannot open a connection to the report server database.

I ran into something interesting today with our VM template.

When I change the machine name, I can run the SQL script on the desktop to correct the SQL server. After reboot, all seems fine. ReportServer services even start up with no error.

However, if I try to run an SSRS report, or even connect to http://localhost/reports, I get an error:

"The report server cannot open a connection to the report server database."

This is because the report server configuration records the SQL server that the ReportServer database resides on (and even the name of the ReportServer database, in case you want to really confuse people by changing it).

If this happens to you, this is how you fix it:

Launch Reporting Services Configuration Manager
Click database from the menu at the left.
On the right, you will see you old SQL Server name. Click change database.
Select Choose an existing report server database, then next
Enter your new SQL Server name and test connection, then click next
For Report Server Database, select ReportServer, then Next x3
Then finish

If you have WennSoft reports setup, you can run this to correct those paths:
WS_SetReplacementReportsForSRS

Friday, January 30, 2015

Moving the tempdb database

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

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

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

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

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

5. Bounce SQL

6. Use windows explorer to delete the old tempdb files

Moving a SQL User Database

To move a SQL user database, follow these steps:

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

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

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

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

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

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

Mighty Code

Wednesday, October 7, 2015

script to move a database

script to move all DYNAMICS databases

Wednesday, June 10, 2015

VB script to send an ftp file after exporting the file with smartconnect

Friday, March 27, 2015

Coalesce in C#

Friday, February 13, 2015

Conditionally stop a trigger from firing

Tuesday, February 10, 2015

The report server cannot open a connection to the report server database.

Friday, January 30, 2015

Moving the tempdb database

Moving a SQL User Database

Creating a SQL job to automatically refresh a test database

SQL Job to refresh TEST from PRODUCTION

SQL 2022 TSQL snapshot backups!

Followers