SQL Server Database Permissions

Overview 

During installation, the user must provide a database connection for the server to operate. A common database users may wish to connect to, for example, is Microsoft Structured Query Language, aka MSSQL. 

However, the System administrator of the SQL database must grant the Server certain permissions to allow users to perform tasks against it, such as:

• Create a database table
• Add entries to a database table
• Configuring indexes 
• Backup the database and/or log
• Run queries on the database and its tables
  
  

Supported SQL Server Versions

SQL 2012 contains increased restrictions on the default permissions for users, so it is recommended to choose SQL authentication over SSPI when using SQL 2012.

Role Assignment Authentication

Multiple database-level roles may be assigned during database configuration using SSPI or SQL Auth.

For further information on database-level roles, refer to Microsoft's "Database-level roles" article.

SSPI

SSPI refers to the Windows OS and its users to identify rights and provide access to the SQL Server.  It is commonly used in Active Directory or LDAP-based enterprises to centralize control and access to systems and services. For further information, refer to Microsoft's "Security Support Provider Interface (SSPI)" article.

SQL Auth

SQL Authentication refers to the SQL Server's settings to log users. It is the recommended authentication method for installations using SQL 2012 due to increased security. For further information on authentication types, refer to Microsoft's "Choose an Authentication Mode" article.

Required Permissions

Database User

The following permissions are the minimum required database permissions levels to install using an SQL server. 

They can be assigned as membership roles and do not need to be owned roles in the SQL server.

• db_datawriter
• db_datareader
• db_ddladmin

In addition, the database service user installing Decisions requires the db_creator role to create a new database. For Azure SQL databases, grant the db_manager role instead.

After granting the user the minimum required permissions listed prior, apply the following explicit permissions to the database user.

Since CONTROL is available across all versions, unlike ALL, most installations should grant CONTROL permission instead.

• ALTER ANY SCHEMA
• EXECUTE
• ALL (SQL 2008) / CONTROL (SQL 2012)

Decisions Database

Grant the following permissions implicitly or explicitly on the Decisions database while running the installer but before starting installation. 

The SQL server's default database does not need to be the same as Decisions's, but the database user should have their default database set to SQL Server.

If the following permissions are not in place, the platform will not correctly install or function. 

• ALTER ANY SCHEMA
• BACKUP DATABASE, BACKUP LOG
• CONNECT
• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW
• EXECUTE

Enabling ALL

The ALL permission may be enabled by either manually selecting it in the database's Properties or by running a query. 

Opening Database Properties 

1. Open and log in 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, grant the required explicit user permissions. Select Ok once done.

Configuring via 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.
   
   Running the following query grants the configured account Admin permissions, including the ALL permission.
   Enter the primary database name, non-admin login name, non-admin login password, and non-admin username values where designated in the below query.
   

   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>'

   
   

    SQL Server versions 2016+ no longer support GRANT ALL. Instead, run the below query to grant ALL.

   USE decisions_database 
   GO 
   GRANT ALTER ANY SCHEMA to decisions_user_name 
   GRANT EXECUTE to decisions_user_name 
   GRANT CONTROL to decisions_user_name 
   EXEC sp_addrolemember N'db_datareader', N'decisions_user_name' 
   EXEC sp_addrolemember N'db_datawriter', N'decisions_user_name' 
   EXEC sp_addrolemember N'db_ddladmin', N'decisions_user_name'

3. Select the Execute button from the top Action bar. 
   
4. Run the installer and then select EDIT SETTINGS.
5. In the Settings screen, edit the DatabaseConnectString so that the User ID and Password values match those for the non-admin user. Then, click Save. 
   
6. To apply these changes, select RESTART SERVICE, then confirm with Yes. After a successful restart, ALL permission will be recognized.

   ---

   For further information on Installation, visit the Decisions Forum.