SQL Server users and application roles

Below description gives a rough overview of the permissions required in SQL Server, as well as how to use an application role. An application role increases security by assigning operational administrators only the necessary permissions for the duration of a session.

Create databases

We recommend that you create the required databases in SQL Server before installing the Scout Enterprise Management Suite.

Please note the following:

• To create databases in SQL Server, you need the SQL Server role dbcreator as a minimum.

• The database names may be freely chosen.

• The tables within the databases are created by the installation routine of the Scout Enterprise Management Suite.

Do not delete the original database when backing up and restoring! The unique database ID must be preserved for the initialization of the license database. For further information, see Troubleshooting.

Permissions for users of the Scout Enterprise Management Suite

SQL server role
For the use of the Scout Enterprise Management Suite, the server role public in general is sufficient. Only if users are to perform additional tasks in SQL Server, extended permissions are necessary. For example, restoring databases requires the dbcreator server role.

SQL database role
On database level, for console-only users in standard environments, the database roles db_datareader and db_datawriter are sufficient.
db_owner is needed, for example, for database activities related to updating to a new Scout version. The same applies for performing any configuration and maintenance activities on the database.

The users must be mapped to the default schema dbo.

For environments with SQL Server clusters, additional permissions such as VIEW SERVER STATE and VIEW ANY DEFINITION are required.

SQL application role

In the case of Windows authentication for SQL Server, users are authorized to also log on to the SQL Server Management Studio. For scenarios like this we recommend using a dedicated user group with limited database rights for console-only users.

Permissions to access SQL Server tables can additionally be controlled and restricted for all databases via a system-wide SQL application role. The name and password of the application role must be defined in the relevant database.

A console user for SQL Server then only needs the database role public in order to execute the stored procedure for activating the application role. Once the application role is active, the connection to SQL Server loses the user permissions and assumes the permissions of the application role. Other databases in which guest has been disabled will be inaccessible to the application role. The application role permissions remain active for the duration of a session.

Configuring console users and an application role in SQL Server Management Studio

The following instructions refer to the Scout database.

1. In SQL Server Management Studio, under Security > Logins, add a new user group (Example: Console users) with the following options:

   Server role	Public
   User mapping > Database role membership	db_datareader
   Securables	Set non-required permissions to Deny. Example on server level: Alter any database > Deny

2. Below the Scout database, under Security > Schemas, add a new schema (Example: Console users schema).
   Under Schema owner, select the Console users.

3. Below the Scout database, under Security > Roles > Application roles, add an application role (Example: Console users role) with the following options:

   Default schema	Console users schema
   Password	Choose freely
   Securables	Select your Scout database and set all permissions except Connect to Grant

Defining the application role in the Scout database

1. To specify the application role data in encrypted mode, in a first step, encrypt the name and password of the role. For further information, see Encrypting values.

2. Back in SQL Server Management Studio, for the Scout database, edit the System table.

3. Add one row for the name and one row for the password of the application role. Paste the encrypted values into the table:

   SystemID	ParamName	ParamVal
   …
   <n>	RNameEx	<encrypted name of application role>
   <n>	RPassEx	<encrypted password of application role>

When the Scout Console is started, the fields are read and the access rights of the application role are set.