Post-Restore Tasks on SQL Server - Intergraph Smart Instrumentation - Administration & Configuration

After your database has been restored, you must create all the required schema logins, define their default database, and grant appropriate access rights. You perform all the post-restore tasks in the MS SQL Server Management Studio.

1. Run the MS SQL Server Management Studio on the target server and log on as the System Administrator.

2. Do the following to create the Admin and SPI_LOGIN schema logins and two logins for each domain:

   1. In the Query window, type:

      Admin user login:

      CREATE LOGIN <Admin schema name> WITH PASSWORD = '<Admin schema password>',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF

      CREATE LOGIN SPIsuper WITH PASSWORD = 'spisuper_pwd',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF

      CREATE LOGIN SPI_LOGIN WITH PASSWORD = 'spi_login_pwd',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF

      Domain user login:

      CREATE LOGIN <Domain schema name> WITH PASSWORD = '<Domain schema password>',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF

      CREATE LOGIN <View-Only Domain schema name> WITH PASSWORD = '<View-Only Domain schema password>',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF;

   2. Repeat the last two CREATE LOGIN commands for each existing domain in your database.

   3. Click Execute Query.

3. When the domain type is Owner operator, create the As-Built schema login for each domain. Also, for each project, create Project and Combined Project schema logins as follows:

   1. In the Query window, type:

      CREATE LOGIN <Domain schema name> WITH PASSWORD = '<Domain schema password>_A_1',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF
      CREATE LOGIN <Domain schema name>_E_<Project Number>, WITH PASSWORD = '<Domain schema password>_E_<Project Number>',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF
      CREATE LOGIN <Domain schema name>_C_<Project Number>,
      WITH PASSWORD = '<Domain schema password>_C_<Project Number>',
      DEFAULT_DATABASE = < database name>, CHECK_POLICY = OFF

   2. Repeat the last two CREATE LOGIN commands for each project defined in each domain.

   3. Click Execute Query.

4. Define a default database for the SPIsuper login as follows:

   1. In the Query window, type:

      ALTER SERVER ROLE sysadmin ADD MEMBER SPIsuper;

   2. Click Execute Query.

5. Check the relationship between the SQL Server logins and the SQL Server database users in the restored database. Run the following SQL to generate the login-user relationship report:

   EXEC SP_CHANGE_USERS_LOGIN 'REPORT'

6. Fix the database user logins according to the generated report if required as follows:

   1. In the Query window, type:

      USE < database name>
      GO
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<Admin schema name>', '<admin login name>'
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'SPI_LOGIN', 'SPI_LOGIN'
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<Domain schema name>', '<Domain logon name>'
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<View-Only Domain schema name>', '<View-Only Domain logon name>'

   2. Repeat the last two EXEC commands for each existing domain in your database.

   3. Click Execute Query.

7. When the domain type is Owner operator, for each As-Built schema of each domain and for each Project and Combined Project schemas, do the following to fix the database user logins according to the generated report, if required as follows:

   1. In the Query window, type:

      USE < database name>
      GO
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<Domain schema name>_A_1','<Domain schema logon name>_A_1',
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<Domain schema
      name>_E_<Project Number>', '<Domain schema logon
      name>_E_<Project Number>'
      EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '<Domain schema
      name>_C_<Project Number>', '<Domain schema logon
      name>_C_<Project Number>'

   2. Repeat the last two EXEC commands for each project defined for each domain.

   3. Click Execute Query.