--- title: "SQL Server Database Permissions" slug: "sql-server-database-permissions" description: "Configure the required Microsoft SQL Server permissions for a Decisions installation and its database connection." updated: 2026-06-29T13:17:10Z published: 2026-06-29T13:17:10Z canonical: "documentation.decisions.com/sql-server-database-permissions" --- > ## Documentation Index > Fetch the complete documentation index at: https://documentation.decisions.com/llms.txt > Use this file to discover all available pages before exploring further. # SQL Server Database Permissions ## Overview During installation, the user must provide a **database** **connection** for the server to operate. A common database user 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. - **SQL** **2008** **R2** - **S****QL** **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](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15)" article. ### SSPI SSPI-Specific Administrator ConsiderationsWith SSPI, Administrators must know which user(s) on the Decisions server will run the Service since they need to be granted the additional necessary permissions to run services and connect to SQL. To ensure permissions are administered correctly, please get in touch with support@decisions.com for help before attempting to use 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](/version-10/docs/integrating-active-directory-with-decisions) 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)](https://docs.microsoft.com/en-us/windows/win32/rpc/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](https://docs.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver15)" 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](/version-10/docs/sql-server-database-permissions#enabling-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**. **![](https://cdn.document360.io/6ef8bcc1-6489-4486-9ad1-83acff7e5df0/Images/Documentation/2021-06-08_13h52_08.png)** 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](/version-10/docs/sql-server-database-permissions#database-user). Select Ok once done.**![](https://cdn.document360.io/6ef8bcc1-6489-4486-9ad1-83acff7e5df0/Images/Documentation/2021-06-08_14h10_311.png)** --- ### 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. ```plsql use ; go CREATE LOGIN WITH PASSWORD ='', DEFAULT_DATABASE = ; go CREATE USER FOR LOGIN go GRANT ALTER ANY SCHEMA to GRANT EXECUTE to GRANT ALL to EXEC sp_addrolemember N'db_datareader', N'' EXEC sp_addrolemember N'db_datawriter', N'' EXEC sp_addrolemember N'db_ddladmin', N'' ``` SQL Server versions 2016+ no longer support GRANT ALL. Instead, run the below query to grant ALL. ```plsql 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. ![](https://cdn.document360.io/6ef8bcc1-6489-4486-9ad1-83acff7e5df0/Images/Documentation/2021-06-08_15h09_20.png) 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. ![](https://cdn.document360.io/6ef8bcc1-6489-4486-9ad1-83acff7e5df0/Images/Documentation/image-1782738909942.png) 6. To apply these changes, select **RESTART SERVICE,** then confirm with Yes. After a successful restart, ALL permission will be recognized.![](https://cdn.document360.io/6ef8bcc1-6489-4486-9ad1-83acff7e5df0/Images/Documentation/image-1782739023908.png)