Minimum SQL permissions

The minimum SQL permissions required on the Blue Prism database for normal operation are:

  • db_datareader
  • db_datawriter
  • All roles prefixed with bpa_. For example:
    • bpa_ExecuteSP_DataSource_bpSystem
    • bpa_ExecuteSP_DataSource_custom
    • bpa_ExecuteSP_System

The roles prefixed “bpa_” are only available once the database has been configured using the in-product Create Database functions or manually using the CreateScript command.

The minimum SQL permissions do not provide appropriate privileges to carry out Create or Upgrade database actions from within the product, therefore an appropriate administrator account will need to be used when any of these actions are required:

  • Create database – dbcreator (server role) or sysadmin (server role)
  • Upgrade database:
    • When deleting the existing database – sysadmin (server role)
    • When not deleting the existing database – sysadmin (server role) or dbowner (database role)

To manually execute the Create or Upgrade database scripts (available via Blue Prism Support) against an existing database, the following SQL permissions are required by the user carrying out the actions:

  • DBCreate: dbcreator (server role) or sysadmin (server role)
  • DBUpgrade: sysadmin (server role) or dbowner (database role)
    • When deleting the existing database – sysadmin (server role)
    • When not deleting the existing database – sysadmin (server role) or dbowner (database role)

Additionally, it is recommended to grant the execute permission to the SQL user running the Blue Prism database to the custom table type <schemaname>.IntIdTableType. See Blue Prism application server, step 12: Configure the Windows service for details on the SQL user. An example of the SQL command is shown below where [dbo] is the <schemaname> and [User] is the SQL user running the Blue Prism database:

Copy 
GRANT EXEC ON TYPE::[dbo].[IntIdTableType] TO [User]
GO

If this permission is not granted, users will be unable to view session logs from Control Room, unless they are an admin user. The Session Logs table will display blank and an error message will show in the Blue Prism Event Logs for the application server, for example:

System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'IntIdTableType', database 'BPv7', schema 'dbo'.