Restore the database access IDs (BPC RM on SQL2000)

Introduction

If you are using userid SA to connect to your database you can ignore this step.

The databases ship with the user ids already installed, but when an MS SQL database is moved from one server to another the internal GUID encoding of the user ids may be different on the destination server and you may find that you can not connect with the riskmanuser account, even though it seems to be present. You can either re-create them or run the provided SQL scripts to repair them.

BPC RiskManager will support connecting to many databases at once, so it is not unusual for you to find that you want to move a database from one server to another, or to duplicate a particular database across unlinked servers. You should do this by either:

1. Using the builtin data transfer system of SQL server, or
2. Backup and restore, and then following the steps in OPTION 2 at these times, as your riskmanuser id may already exist on the target recovery server.

Note also that if you are going to use more than one riskman database at once on the same database server, you will have to use the backup and restore (or equivalent duplication) method to install the database, rather than attaching, because the server will think your second attempt to attach a copy of the same database is trying to reuse the datafiles of the first and get difficult about attaching it.

OPTION 1 (If you performed Step 1 as instructed)

• The relevant scripts can be found in:

Scripts are in:

[RMInstallDir] \Database\Scripts\2000\ 

Steps to reconnect the user ID’s for a restored/recovered/attached database:

1. In SQL Enterprise Manager Studio, navigate to the database name under the databases folder and select it.
2. Open MS Query Analyser or equivalent sql query processor on the database and copy and run the provided scripts: updateLoginRMU.sql and fix_executerights_on_loginRole2000.sql. The first script attempts to connect the databases version of riskmanuser with the server’s version of the same user id. The second ensures that that RiskManRole has execute access to our stored procedures in the database.
3. Navigate to the riskmanuser id under the “security” folder and “logins” at the server level and right click and choose properties.
   
   
   
   
4. Select the “DataBase Access” tab and tick the database we just restored.
5. Verify that the database roles ‘RiskManRole’, db_datareader, and db_datawriter have been allocated to the riskmanuser id at the server level. If not tick them to grant these roles. Now select “OK”.
   
   
   
   
6. If you still can’t connect the server’s riskmanuser id to the database – delete it from the database level and follow option 2.

OPTION 2 (If something went wrong)

Steps to create the user ID’s:

1. Creating login ‘riskmanuser’ and choose an appropriate password – you will need to remember this for later (it should already exist in the database, but you may need to delete it if you try to grant access from the top level security branch – it should then be recreated automatically in the database).
2. Delete the riskmanuser id from the database (NOT THE SERVER) you just restored
3. Assign login access to risk database(s) (at the server level).
4. Assign database user membership to database roles: db_datareader, db_datawriter (at the server level).
5. Assign database role ‘RiskManRole’ to the riskmanuser id (at the server level).

If riskmanuser has not been created successfully the application server will not connect at all when you attempt to connect later. Option 2 should always recover the access.

In the event that you can connect from the application server, and perhaps even login from a user account
 
via the client, but not create risks, etc, the problem will most likely be the stored procedure access

rights which are held in the RiskManRole.  Run the “fix_executerights_on_loginRole.sql.” to fix this 

problem.

BackLinks

• Instaling BPC RiskManager Database on SQL Server 2000