Set Up the Target System in a SQL Environment

Check ownership of tables and stored procedures in SQL server

1. Select Start > All Programs > Microsoft SQL Server 2014 > SQL Server Management Studio.
2. Expand the Databases folder to expand down to the BBLEARN database. Expand that level to view all components of the BBLEARN database.
3. Select the Tables folder in the left pane to show all tables in the BBLEARN database.
4. Double-click the Schema column in the right pane to sort the column values. All items should be owned by dbo. Scroll through the list to ensure all items are owned by dbo. Repeat this step for the Stored Procedures folder in the BBLEARN database. Double-click the Schema column to sort the column values. All items should be owned by dbo.

Change the ownership of an object in SQL server

Browse the tables and stored procedure by selecting the tables or stored procedure folders in the SQL Server Management Studio. Look for tables or stored procedures owned by user BBLEARN. Make a list of each table or stored procedure found that will require a change of ownership. Use the following steps to change the ownership:

1. Select Start > All Programs > Microsoft SQL Server 2014 > SQL Server Management Studio.
2. Expand the Databases folder to expand down to the BBLEARN database.
3. Highlight the BBLEARN database.
4. Select New Query to open Query Analyzer.
5. Copy the following statement into the query window:

   sp_changeobjectowner 'BBLEARN.BBLEARN.$object_name$', 'dbo'

6. Insert the name of the table or stored procedure into the above query that needs to be changed from BBLEARN to dbo in to the $object_name$ field.

   Stored procedure change example:

   sp_changeobjectowner 'BBLEARN.BBLEARN. attempt_cp','dbo'

   Table change example:

   sp_changeobjectowner 'BBLEARN.fix_gradebook_main','dbo'

7. Select Run or press F5 to execute the edited query. The following message will be displayed, which indicates that the ownership has been changed successfully.

   Changing any part of an object name could break scripts and stored procedures.

8. Repeat Steps 1 through 7 to change each stored procedure and/or table from BBLEARN to dbo.
9. After completing the change object ownership, the Administrator must re-run the restore script from this document and proceed with the remaining steps in the data restore.
10. Restart the server or servers.

The restore script must be executed again after changing the ownership of an object. If it is not, the application will not connect to the database after completing the remaining steps in the restore document.

Change the hostname settings to match the new server

The following SQL queries must be run on the new server to change the hostname and fullhostname values within the BBLEARN_ADMIN database, assuming these values have changed.

The two BBLEARN_ADMIN tables that are updated are the bbadmain.bb_instance table and the BBLEARN_ADMIN.bb_instance_host table. The update is performed after the data is restored to a new destination server.

1. Select Start > All Programs > Microsoft SQL Server 2014 > SQL Server Management.
2. Select the expanded Databases folder to expand to the BBLEARN_ADMIN database.
3. Highlight the BBLEARN_ADMIN database.
4. Select New Query to open Query Analyzer.
5. Update the db_host, db_pass, stat_db_host, and stat_db_pass for each BBUID. The first BBUID is called BBLEARN by default. If the port numbers are different, these must be updated.

   BB_INSTANCE TABLE
   UPDATE bb_instance SET
   db_host = 'NEW_DB_HOST_NAME',
   db_pass = 'NEW_PASSWORD',
   stat_db_host = 'NEW_STAT_DB_HOST_NAME',
   stat_db_pass = 'NEW_STAT_DB_PASSWORD'
   Where bbuid = 'BBLEARN';

6. Select Run or F5 to execute script after loading it into the query window.
7. The bb_instance_host table contains the fullhostname that is used to access the blackboard server using the URL.

   hostname.domain_name.com
   BB_INSTANCE_HOST TABLE
   UPDATE bb_instance_host SET hostname= 'TARGET_HOST_HAME.domain_name.com'
   WHERE hostname= 'SOURCE_HOST_NAME.domain_name.com';