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