Minimum SQL Permissions for Planning Only Users

Overview

The Solver Planning application performs many elevated permission features such as updates, inserts, deletes, etc. For companies with tight SQL security that want to configure users with minimal permissions in Planning, the following can be done. 

• Applying these permissions will reduce/restrict functionality within the Data Warehouse application

Steps

The following should be configured y someone familiar with the steps to configure SQL security. 

1. First connect to the SSMS server and open the Security dropdown.
2. Make sure all users exist under the dropdown and if they don’t then add them.
3. Make sure that the user(s) have ‘public’ checked under Server Roles
4. db_datareader and public checked for the Repository
5. db_datareader, db_datawriter, and public checked for all Data Warehouse databases the user(s) will be connecting to(under User Mapping),
6. Click the ‘OK’ button.
7. Grant Insert and Update permissions for the user(s) on the following tables of the Repository database
   1. IntegrationSettings
   2. DataSourceSettings
   3. LicenseValidation
8. Update permissions on table Users for the Repository database.
9. Grant Execute permissions on tables GetDBVersion and GetPlanningRightsDetails for all Data Warehouse databases that the user(s) will be connected to.

The user(s) need to then log into Planning and connect to a Data Warehouse database. Once that has been done, remove Insert and Update permissions for the user(s) on tables LicenseValidation and Users for the Repository database.

You now have users sets up with minimal permissions.