Configuration of DB ROLES for your own database.

This article can be used for configuration of roles in your own MS SQL database during Safetica implementation.

For Safetica to run correctly, there are 3 possible configurations for user roles in MS SQL database.

1. DB_owner

This is the lowest required role for the basic functionality of Safetica, except for automatic actualization, data archivation and advanced configuration. – This setup is not recommended.

The account used by Safetica to access the database must have access rights to all Safetica databases with the minimal role of DB_owner.

http://www.sqlservercentral.com/articles/Security/sqlserversecurityfixeddatabaseroles/1231/

2. DB_creator

DB_creator is a sufficient role, however, it’s unable to provide archivation option outside of the existing SQL folder. – This setup is recommended.

The account used by Safetica to access the database must have apart from DB_owner role (previous point) also server role db_creator with explicit authorization VIEW SERVER STATE, which allows us to provide database maintenance.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles

3. SA (sysadmin)

SA (sysadmin) role is required for advanced setup and configuration. For example, archivation outside of the existing SQL folder, advanced debugging or, in the case of SQL Server version 10.50.2500 and lower (2008 R2 SP1 and lower) – This setup is not necessary.

The account used by Safetica to access the database must have apart from roles mentioned in both previous points (DB_owner, DB_creator), also additional roles: ALTER SERVER STATE, ALTER SETTINGS, ALTER TRACE, ALTER ANY EVENT SESSION.

The easiest way to achieve fine-tuning and optimization is to temporarily allow SA (sysadmin) role.

In the case of version 2008 R2 SP1 and lower, SA (sysadmin) role is necessary for collecting data regarding free DB and HDD space via function XP_FIXEDDRIVES.