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, associating applications, and backing up and restoring sites and plants. Users with these privileges enter their own user name and password, 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
Grant Backup Privileges for a User
When backing up a site or plant, the SEM Admin must have a database level role membership of db_backupoperator if db_owner database role is not assigned to SEM Admin login. For example:
USE <Site/Plant database name>
GO
ALTER ROLE db_backupoperator ADD MEMBER USER_ADMIN
GO
Grant Restore Privileges for a User
When restoring a site or plant, the SEM Admin must have, in addition to the database roles db_ddladmin and db_owner, a server level role membership of dbcreator. For example:
USE <Site/Plant database name>
GO
ALTER ROLE dbcreator ADD MEMBER USER_ADMIN
GO