View source for Restore the database access IDs (BPC RM on SQL2005)

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


# Using the builtin data transfer system of SQL server, or
# 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:<br>
 [RMInstallDir] \Database\Scripts\2005\ 


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

<ol>
<li> In SQL Server Management Studio, navigate to the database name under the databases folder.
<li> Right click on the database in the tree view and choose ‘new query’ on the database and copy the provided script: “updateLoginRMU.sql” into the query window and run it by selecting “Execute”.
<br>
<br>
[[Image:SQLStudMan_AssignUserRights0.png]]
<br>
<br>
<li> Right click on the database in the tree view and choose ‘new query’ on the database and copy the provided script “fix_executerights_on_loginRole2005.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.''
<li> Expand the Security folder for the server (not the database!!) and expand the “logins” folder.  Right click on the “riskmanuser” and choose properties.
<br>
<br>
[[Image:SQLStudMan_AssignUserRights1.png]]
<br>
<br>
<li> Select “User Mapping” from the left hand list and tick the restored database in the top panel.  In the panel below that, verify that database role public, ‘RiskManRole’, db_datareader, and db_datawriter have been tisked for the id.  If not, tick them to grant these roles.  Select OK.  If you still can’t connect the server’s riskmanuser id to the database – delete it from the database level and follow option 2.
<br>
<br>
[[Image:SQLStudMan_AssignUserRights2.png]]
<br>
<br>
</ol>


=OPTION 2 (If something went wrong)=


Steps to create the user ID’s:

# 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).
# Delete the riskmanuser id from the database (NOT THE SERVER) you just restored (and the riskmanuser schema in 2005)
# Assign login access to risk database(s) (at the server level).
# Assign database user membership to database roles: db_datareader, db_datawriter (at the server level).
# 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<br> 
 via the client, but not create risks, etc, the problem will most likely be the stored procedure access<br>
 rights which are held in the RiskManRole.  Run the “fix_executerights_on_loginRole.sql.” to fix this <br>
 problem.''



=BackLinks=

{{#dpl: linksto={{FULLPAGENAME}} }}