SQL Server Database Permissions

Decisions and SQL Server

Versions

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 it is strongly recommended to use SQL authentication instead of SSPI.

Authentication

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 the SQL server. Still, you must be aware of what user(s) 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 seek support from your IT administrator before using this configuration.

SQL Auth

SQL authentication is a system of logging in users based on settings in the SQL server itself.  This lacks central control but is very straightforward to configure.  It is recommended to use SQL auth with SQL 2012 due to the increased security in SQL 2012.

Standard User

The Decisions installer and platform take care of most Database activities for an application.  This includes many of the following:

1. Creating an initial Database or tablespace

2. Creating and altering tables

3. Configuration of Indexes

To perform these activities, the installer and service need to have the "db_creator" permissions on the Database.

Limiting User

There is the ability to create a limited user in the SQL server, but this requires doing some of the Database setup and configuration manually.

To configure Decisions in this manner, create a new Database in the SQL server.  Once the Database is named, identify, or create a user that will use this Database.  Using the tools, grant this user rights on the Database that was created. 

• db_datawriter

• db_datareader

• db_ddladmin

These roles can be assigned as ‘membership roles’ and do not need to be ‘owned roles’ in SQL Server.
The Database user must also have the following explicit permissions on the Decisions Database:

• ALTER ANY SCHEMA

• EXECUTE

• ALL (*see section below for more information)

Before installation and while Decisions is in use, the 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

• CONNECT,

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW

• EXECUTE

Enabling ALL

Note that the ALL permission has since been deprecated and can only be applied by using a query or through manual selection under Database Properties.

Via Database Properties 

To enable ALL via Database Properties:

1. Open and login to Microsoft SQL Server Management Studio. 
2. From the Object Explorer in Microsoft SQL Server Management Studio, right-click the desired Database and select Properties. 
3. Navigate to the Permissions page and select the desired User from the Users or roles list.
4. From the Permissions for [username] list, check Grant for the following permissions, then click OK. 
   • Backup database, Backup log
   • Connect
   • Create default, Create function, Create procedure, Create rule, Create table, Create view
     

Via Query 

To enable ALL via Database Query: 

1. From Microsoft SQL Server Management Studio, right-click the desired Database and select New Query.
2. In the resulting Query tab, input the following text. 
   Note on Query 

   Running the following Query give the configured Account Admin permissions as well as the ALL permission. To properly set it up, set the primary database name, non-admin login name, non-admin login password, and non-admin user name values to respective values. 

   use <primary database name>;
   go
   CREATE LOGIN <non-admin login>
   WITH PASSWORD ='<non-admin login password>', DEFAULT_DATABASE = <primary database name>;
   go
   CREATE USER <non-admin user> FOR LOGIN <non-admin login>
   go
   GRANT ALTER ANY SCHEMA to <non-admin user>
   GRANT EXECUTE to <non-admin user>
   GRANT ALL to <non-admin user>
   EXEC sp_addrolemember N'db_datareader', N'<non-admin user>'
   EXEC sp_addrolemember N'db_datawriter', N'<non-admin user>'
   EXEC sp_addrolemember N'db_ddladmin', N'<non-admin user>'

   
   

3. Select the Execute button from the top Action bar. 

   
   

4. Open DecisionsServerInstaller.exe and select EDIT SETTINGS. From the Settings screen, edit the DatabaseConnectString so that the User ID and Password values match the non-admin user Account. Then, click Save. 

   
   

5. Back on the primary Decisions Installer screen, select RESTART SERVICE then confirm with Yes.