9.1 SP1 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.1 SP1. 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.

  • 910sp1_01_RxTables_mssql.sql

  • 910sp1_02_ArcJoinViews_mssql.sql

  • 910sp1_03_GenerateGrants_mssql.sql

  1. Open the SQL Server Query Analyzer window, connecting to the Responder database as the schema owner (e.g., RESPONDER).
  2. Execute the 910sp1_01_RxTables_mssql.sql script. This script creates the database tables required by Responder.
  3. The 910sp1_02_ArcJoinViews_mssql.sql script creates the views that are used to display Responder data in ArcMap. Before executing this script, you may 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. Execute 910sp1_02_ArcJoinViews_mssql.sql.
  4. Open 910sp1_03_GenerateGrants_mssql.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run. 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:
    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

    The first four lines assign permissions to a role for tables. The last four lines assign permissions to a role for views (views can only be assigned the Select permission). 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.

  5. The "grant insert, update, delete, select" portion determines which permissions are to be assigned. Modify these permissions as necessary.
  6. Replace "rx_editor" with the name of the role to assign the permissions.
  7. When you've finished adding statements for roles, close and save the file.
  8. Open 910sp1_03_GenerateGrants_mssql.sql in SQL Server Query Analyzer.
  9. Right-click in the top grid (with the query) and select Results in Text.
  10. Execute 910sp1_03_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 ;
  11. 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.
  12. Execute the copied query to assign permissions to roles. The permissions assigned apply to all Responder tables and views.
QR code for this page

Was this helpful?