SQL Server Database PermissionsLast Updated: 12/03/2015 Introduced in Verision: 2.0
Decisions and SQL Server
Decisions can be used with SQL 2008 R2 or SQL 2012. With SQL 2012 there are increased restrictions on the default permissions for automatically setup users, so we strongly recommend using SQL authentication instead of SSPI.
SSPI, or Integrated Security, uses the Windows environment and users to identify rights and access to the SQL server system. This is commonly used in Active Directory or LDAP based enterprises to centralize control and access to systems and services. SSPI can be used with SQL server, but you must be aware of what user on the server will be running the Service Host Manager service and ensure that the user is granted the appropriate permissions to run services and connect to SQL. Please get support from your IT administrator to use this configuration.
SQL authentication is a system of logging in users based on settings in SQL server itself. This lacks central control, but is very straightforward to configure. We recommend using SQL auth with SQL 2012 due to the increased security in SQL 2012.
The Decisions installer and platform take care of most database activities for an application. This includes many of the following:
- Creating an initial database or tablespace
- Creating and altering tables
- Configuration of Indexes
In order to perform these activities the installer and service need to have the following permissions on the database:
You can create a much more limited user in SQL server, but this requires doing some of the database setup and configuration for Decisions manually.
If you want to configure Decisions in this manner you first need to create a new database in SQL server. Once you have named this database you need to identify or create a user that you will use for this database. Using your tools you will want to grant this user rights on the database that you’ve created. Due to the dynamic nature of the workflow engine, rights to create and alter tables, as well as execute stored procedures, are absolutely required. The following roles must be assigned to your database user for the Decisions database:
These roles can be assigned as ‘membership roles’ and do not need to be ‘owned roles’ in SQL Server.
Your database user must also have the following explicit permission on the Decisions database:
- ALTER ANY SCHEMA
Before installation and while Decisions is in use, your database user must have all of the following permissions granted on the Decisions database, either implicitly or explicitly:
- ALTER ANY SCHEMA
- BACKUP DATABASE
- BACKUP LOG
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
You should also configure this user’s default database to use in SQL Server, although this default DB does not need to be the same database as Decisions is using.
Note: if these permissions are not in place, Decisions will not install correctly and will not function correctly post installation.