After your Smart Instrumentation 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.
-
Run the MS SQL Server Management Studio on the target server and log on as the System Administrator.
-
Do the following to create the Admin and SPI_LOGIN schema logins and two logins for each domain:
-
In the Query window, type:
Admin user login:
CREATE LOGIN <Admin schema name> WITH PASSWORD = '<Admin schema password>',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFFCREATE LOGIN SPI_LOGIN WITH PASSWORD = 'spi_login_pwd',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFFDomain user login:
CREATE LOGIN <Domain schema name> WITH PASSWORD = '<Domain schema password>',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFFCREATE LOGIN <View-Only Domain schema name> WITH PASSWORD = '<View-Only Domain schema password>',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFF; -
Repeat the last two CREATE LOGIN commands for each existing domain in your database.
-
Select Execute Query.
-
-
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:
-
In the Query window, type:
CREATE LOGIN <Domain schema name> WITH PASSWORD = '<Domain schema password>_A_1',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFF
CREATE LOGIN <Domain schema name>_E_<Project Number>, WITH PASSWORD = '<Domain schema password>_E_<Project Number>',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFF
CREATE LOGIN <Domain schema name>_C_<Project Number>,
WITH PASSWORD = '<Domain schema password>_C_<Project Number>',
DEFAULT_DATABASE = <Smart Instrumentation database name>, CHECK_POLICY = OFF -
Repeat the last two CREATE LOGIN commands for each project defined in each domain.
-
Select Execute Query.
-
-
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'
-
Fix the database user logins according to the generated report if required as follows:
-
In the Query window, type:
USE <Smart Instrumentation 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>' -
Repeat the last two EXEC commands for each existing domain in your database.
-
Select Execute Query.
-
-
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:
-
In the Query window, type:
USE <Smart Instrumentation 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>' -
Repeat the last two EXEC commands for each project defined for each domain.
-
Select Execute Query.
-