About connecting to SQL Server with an account that has administrative privileges (Basic) - Security Center 5.12

Security Center Hardening Guide 5.12

Product
Security Center
Content type
Guides > Administrator guides
Version
5.12
Language
English
Last updated
2024-07-04

Security Center does not require the SQL sysadmin server role on the database server. Each Security Center role requires a different set of permissions.

Server-level roles

A broader set of permissions is necessary during the first execution of Security Center for the creation of the Security Center role databases. Therefore, it is possible to restrict the permission set after the first execution. You can also start with the restricted permission set by creating the required databases outside of Security Center. Refer to the table below for more information.

The Directory role requires the VIEW SERVER STATE permission to work properly. This permission is mandatory when Directory failover is configured. VIEW SERVER STATE should always be enabled.

The processadmin server-level role is required to obtain the VIEW SERVER STATE permission (refer to this document to see the permissions associated with the fixed SQL Server role). In return, this permission grants access to the server-scoped Dynamic Management Views and functions.

The most important function of this permission is sys.dm_tran_locks . This function allows the Directory role to lock the Directory database to prevent the failover mechanism from taking over. The lock function is crucial to guarantee that only one Directory server ever controls the Directory database at any time. This function is a server-wide mechanism and therefore requires VIEW SERVER STATE permission.

Another function used is sys.dm_exec_connections in the Security Widget to figure out whether all roles use encrypted connections. The purpose of this function is informational and does not affect the operation if its call fails.

The public server-level role allows the execution of stored procedures created by default in SQL Server. Revoke the EXECUTE permission of the xp_dirtree stored procedure.

Server-level roles
Roles public dbcreator processadmin
Access Manager X X1 X
ALPR Manager X X1 X
Archiver X X1 X
Automation Manager2 X X1,3,4 X
Auxiliary Archiver X X1 X
Directory X X1,3,4 X
Health Monitor X X1 X
Intrusion Manager X X1 X
Media Router X X1 X
Mobile Credential Manager X X1 X
Mobile Server X X1 X
Plugin (roles with their own database) X X1 X
Plugin (roles connecting to another role's database) X - -
Point of Sale X X1 X
Record Caching Service X X1 X
Unit Assistant X X1 X
Zone Manager X X1 X

1 dbcreator is only necessary if you want Security Center to create the databases for you; and only for the first system execution. Remove it after the first execution. You also have the option to create empty databases yourself. When the system runs for the first time, the service user creates the tables in empty databases, and therefore the dbcreator role is not needed.

2 The Automation Manager role is created when you turn on the Automation feature. For more information, see the Security Center Automation Preview Guide on the TechDoc Hub.

3 dbcreator is necessary when using Directory database failover through backup and restore. If database failover through backup and restore is not used, dbcreator is only necessary for the first system execution, and only if you are letting the system create the Directory database.

4 dbcreator is also necessary during a Security Center upgrade, when the Automation feature is enabled. In this case, an extra database is created beside the Directory database, named DirectoryAutomationEvents. This database is used as an event cache, when the connection between the Directory and the Automation Manager is lost.

Database-level roles

Databases are created during the first execution of a Security Center role.

The db_owner role is automatically created on the databases of Security Center roles after their creation. However, Security Center roles only need the database-level roles listed in the following table during normal operations.

The public database-level role is required to list the databases for selection in Config Tool. Security Center roles using dynamic schemas require the db_ddladmin database-level role to alter their database schema. They need to constantly create new tables, new columns, new indexes, and so on. For this reason, they need to operate as database owners.

There is the db_owner role in SQL Server that grants full permission on the database. However, assigning this role to Security Center roles defeats the purpose of reducing their permissions. Instead of using the db_owner role, replace it with the less privileged db_ddladmin role. The latter allows you to create and alter tables without the full permission of the db_owner role.

Database-level roles
Roles public db_datareader db_datawriter db_backupoperator db_ddladmin
Access Manager X X X X X
ALPR Manager X X X X X
Archiver X X X X -
Automation Manager X X X X X
Auxiliary Archiver X X X X  
Directory X X X X X
Health Monitor X X X X X
Intrusion Manager X X X X -
Media Router X X X X -
Mobile Credential Manager X X X X X
Mobile Server X X X X  
Plugin (roles with their own database) X X X X X
Plugin (roles connecting to another role's database) X X X - -
Point of Sale X X X X X
Record Caching Service X X X X X
Unit Assistant X X X X  
Zone Manager X X X X X
NOTE: Security Center roles require the EXECUTE permission on the dbo schema. This permission can be granted by using the following T-SQL command on each database:
Code
GRANT EXECUTE ON SCHEMA::[dbo] TO [ principal used by the Security Center role ]