Create SQL Server Admin Users with Limited Privileges - Intergraph Smart Engineering Manager - 3.0 - Installation & Upgrade - Hexagon

Intergraph Smart Engineering Manager Web Installation and Configuration

Language
English
Product
Intergraph Smart Engineering Manager
Subproduct
Web
Search by Category
Installation & Upgrade
Smart Engineering Manager Web Version
3.0

When working on a SQL Server platform, you can create a database user with limited privileges that allow this user to perform Smart Engineering Manager procedures such as creating sites and plants, and associating applications. Users with these privileges enter their own user names and passwords, thus avoiding the need for the database user to know the Sys Admin password.

Create an Admin User (USER_ADMIN)

The following is an example script that can be run to create a user login and password on the server:

USE [master]

GO

CREATE LOGIN USER_ADMIN WITH PASSWORD=N'USER_ADMIN', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO

  • When a Server Admin user with limited privileges is created as described in this topic, such a user does not have permission to delete plant logins. For this reason, after a Server Admin user with limited privileges deletes a plant, the logins must be deleted manually in the SQL Server database.

  • Before you launch the Upgrade Utility, make sure the System user has a sysadmin server role.

  • The admin user name and password (USER_ADMIN) used in the above script are for example only. You can define as many admin users as you want, each with their own login and password.

  • Each admin user remains in the database but has no privileges unless granted the database-level privileges as described below.

Grant the Admin User Database-Level Privileges

Run the following script:

GRANT ALTER ANY LOGIN TO [USER_ADMIN]

Create New Database with Specific User and Roles

The SQL Server Administrator needs to map the SEM Admin (USER_ADMIN in our example) to each database created as a prerequisite for site and plant creation, and the application association workflow. In each database for which the login was created, the following minimum database roles should be defined:

  • db_ddladmin

  • db_owner

For example:

Create database_<Site/Plant database name>

USE <Site/Plant database name>

GO

CREATE USER USER_ADMIN FOR LOGIN USER_ADMIN

GO

USE <Site/Plant database name>

GO

ALTER ROLE db_ddladmin ADD MEMBER USER_ADMIN

GO

USE <Site/Plant database name>

GO

ALTER ROLE db_owner ADD MEMBER USER_ADMIN

GO