Skip to content

SQL Server Login Permissions for MES Installation and Operation#

This section provides essential information regarding the database user required to install and run the MES system.

To successfully install or upgrade MES to a newer version, the designated database user must be assigned the sysadmin role in SQL Server. This elevated permission is necessary to ensure that the installation or upgrade process completes without issues.

Once the MES system is fully operational, the sysadmin role can be removed from the database user, as it will no longer be required for regular system operations.

Removing sysadmin Role from Database User#

This section outlines the necessary steps and permissions required to remove the sysadmin role from the database user, once the MES system is up and running.

Warning

This process involves removing the sysadmin role from the existing database user, not replacing the user itself.

By removing the sysadmin role, the database user will retain ownership of the MES databases and associated jobs. This ownership grants most of the necessary permissions to ensure that the system continues to operate correctly.

Therefore, before removing the sysadmin role from the database user, please ensure that the following conditions are met.

Warning

If the environment is configured with SQL Server Always On, the following configurations must be performed on all database servers.

Configure User Mapping for MES Linked Servers#

To ensure proper functionality before removing the sysadmin role, you need to configure user mapping for the MES Linked Servers as follows:

  • Local Login: The database user specified during MES installation
  • Impersonate: No
  • Remote User: The same database user specified during MES installation
  • Remote Password: The password for the database user specified during MES installation

This user mapping must be applied to the following Linked Servers:

  • cm{system_name}ODSLink
  • cm{system_name}DWHLink
  • cm{system_name}OnlineLoopback
  • cm{system_name}OnlineLink

If the environment is configured with SQL Server Always On, the user mapping must also be applied to the following Linked Servers:

  • Online High Availability LinkedServer
  • ODS High Availability LinkedServer
  • DWH High Availability LinkedServer

Configure Database User Permissions#

Ensure that the database user is granted ⧉ the following permissions:

  • VIEW SERVER STATE
  • VIEW ANY DEFINITION

Configure User Mapping for the msdb Database#

To ensure proper operation of the MES system, the database user must have a user mapping to the msdb database. Additionally, the following permissions must be granted within the msdb database:

GRANT SELECT ⧉ on the following tables:

  • dbo.sysjobhistory
  • dbo.sysjobsteps
  • dbo.sysjobs
  • dbo.sysjobservers

Trusted Assemblies#

The MES databases include and utilize certain custom assemblies (using CLR) that provide various utility functions and procedures. To ensure these assemblies can be used without the sysadmin role, they must be added to the list of trusted assemblies for the server ⧉.

The following assemblies need to be added to the trusted assemblies list for each respective database:

  • Online:

    • OnlineDatabaseUtilsCLR

  • Operational Data Store (ODS):

    • cmFoundationSQLServerCLRUtils
    • ICSharpCode.SharpZipLib

  • Data Warehouse (DWH):

    • cmFoundationSQLServerCLRUtils
    • ICSharpCode.SharpZipLib
    • System.IO.Compression