Create an SQL Server login

If the MES middleware or curator host services are going to use SQL authentication to access the MES database, an SQL Server database administrator must create the middleware service or curator host service SQL Server login and grant the roles and minimal permissions to it.

Proper permissions are granted by assigning the following SQL Server database roles to the SQL Server login.

• Alter — Required to add attributes that will be seen in the work queue and the inventory grid, which alters tables in the MES database.

• Connect — Required to connect to the MES database.

• Delete — Required to be able to delete records from the MES database.

• Execute — Required to execute SQL commands on tables in the MES database.

• Insert — Required to be able to insert records into the MES database.

• Select — Required to be able to do selections on the MES database.

• Update — Required to be able to update records in the MES database.

It is important that you do not grant these database roles to the SQL Public server role. Instead, they should be granted to the middleware service SQL Server login. This login should be assigned the Public server role, as described in the following procedure.

The screen shots in this procedure are based on SQL Server Management Studio 18. Depending on what version you are using, the steps and screens might be different.

Create a SQL Server login for the MES database

1. If the MES database has not been created yet, run the MES installation and configure the MES Database Setup component in the post-install Configurator to create it.

2. Log into SQL Server Management Studio as a user with administrator rights.

3. In the Object Explorer window, create a SQL Server login by right-clicking Logins under Security and then clicking New Login.

   

   The Login - New dialog appears.

   

4. In the General settings, select SQL Server authentication, enter the login name and password, and clear Enforce password policy.

5. In the Server Roles settings, assign the MES middleware service or Curator Host service login the Public server role.

   

6. In the User Mappings settings, select the MES database the service requires access to.

   

7. Select OK to save the SQL login.

   The dialog closes and the login is added to the list in Logins under Security in the Object Explorer window.

8. In the Object Explorer window, under Databases right-click the MES database and click Properties.

   The Database Properties window appears.

9. In the Permissions settings, select the MES middleware service SQL Server login you just created in the User or roles list.

10. Select the Grant check box for each of the required database roles: Alter, Connect, Delete, Execute, Insert, Select, and Update.

    

11. Select OK to save the assignments.

You can now use the username and password of this SQL Server login for:

• The Database Connection settings of the DB/MW Communications component Production and Restore tabs.

• The Staging Curation Setting of the MES Cloud Integration component AVEVA Identity Manager tab.