9.2 Upgrade Scripts - SQL Server

The following implementation scripts must be run on your Responder SQLServer database to add the correct tables, views, and indices to upgrade Responder to version 9.2. When upgrading Responder execute ONLY the Upgrade script(s) that fall between your previous release and the new release to which you are upgrading. Do NOT execute any Implementation Scripts.

Responder requires two databases: Responder and Archive. Some scripts are run on the Responder database and some on the Archive database. In the list below, the database on which the script should be executed is in parentheses.

920_01_RxTables_mssql.sql (Responder)

920_02_ArcJoinViews_mssql.sql (Responder)

920_03_ArchiveTables_mssql.sql (Archive)

920_04_GenerateGrants_mssql.sql (Responder and Archive)

Open the SQL Server Query Analyzer window, connecting to the Responder database as the schema owner (e.g., RESPONDER).
Execute the 920_01_RxTables_mssql.sql script. This script creates the database tables required by Responder.

The 920_02_ArcJoinViews_mssql.sql script creates the views that are used to display Responder data in ArcMap. Execute 920_02_ArcJoinViews_mssql.sql. Even if you executed this script as part of the initial implementation, execute it again.

Before executing this script, you will need to modify the feature class names within it to match those in your geodatabase. Open the script and ensure that the feature class names in the script match the corresponding feature class names in your geodatabase. If you have done this for a previous release, you may use the prior script and copy/paste the new views into it. This would prevent the need to re-update feature class names for all views.

Open the SQL Server Query Analyzer window, connecting to the Archive database as the schema owner (e.g., ARCHIVE).
Execute the 920_03_ArchiveTables_mssql.sql script. This script creates the database tables required by Responder Archive Explorer.

Open 920_04_GenerateGrants_mssql.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run on both the Responder and Archive databases. This script ensures permissions are assigned to tables created by the RxTables and ArchiveTables scripts run in previous steps. Before executing, you must determine the roles and permissions to assign them. You may assign permissions to several different roles. Look for the following lines of code in the script:

-- rx_editor role can edit data in responder or archive schema
select 'grant insert, update, delete, select on ' 
+ table_name + ' to rx_editor ;' 
from information_schema.tables where table_type = 'base table' and table_schema = 'responder'
go
select 'grant select on ' 
+ table_name + ' to rx_editor ;' 
from information_schema.tables where table_type = 'view' and table_schema = 'responder'
go

-- rx_viewer role can view data in responder schema
select 'grant select on ' 
+ table_name + ' to rx_viewer ;' 
from information_schema.tables where table_type = 'base table' and table_schema = 'responder'
go
select 'grant select on ' 
+ table_name + ' to rx_viewer ;' 
from information_schema.tables where table_type = 'view' and table_schema = 'responder'
go

The table_schema = 'responder' portion above refers to the database owner (Responder).

The first four lines assign permissions to a role for tables. The second set of four lines assign permissions to a role for views (views can only be assigned the Select permission). The last eight lines perform the same tasks as the first half, but for views rather than tables. You will need a set of lines for each role to assign permissions to tables and views. The next few steps describe how to perform the modifications you can make for each role.

You may execute the same script for the Responder database and Archive database. The script MUST be executed on both databases.

The "grant insert, update, delete, select" portion determines which permissions are to be assigned. Modify these permissions as necessary.
Replace "rx_editor" with the name of the role to assign the permissions. There are two possible roles: rx_editor and rx_viewer.
When you've finished adding statements for roles, close and save the file.
Open 920_04_GenerateGrants_mssql.sql in SQL Server Query Analyzer.
Right-click in the top grid (with the query) and select Results in Text.

Execute 920_04_GenerateGrants_mssql.sql. Remember, this script only generates a script that sets permissions. Below is an example of the resulting script:

grant insert, update, delete, select on RX_TAG_GROUND_STEPS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_TAGS_AND_GROUNDS to rx_editor ;
grant insert, update, delete, select on RX_TAGS_AND_GROUNDS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_TRUCKS to rx_editor ;
grant insert, update, delete, select on RX_TRUCKS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_USERS to rx_editor ;

(41 row(s) affected)

                                                                                                                                                                
--------------------------------------------------------------------------------------- 
grant select on Current_Outages to rx_editor ;
grant select on RX_CREW_ASSIGNMENT_COUNT_VIEW to rx_editor ;
grant select on RX_CREW_INCDEV_DPD_VIEW to rx_editor ;
grant select on RX_CREW_INCDEV_FUS_VIEW to rx_editor ;

Remove the highlighted portions above and any extra empty returns. Copy the script and paste it into a new query. Copy only the "grant..." lines.
Execute the copied query to assign permissions to roles. The permissions assigned apply to all Responder tables and views.