What are the minimum permissions required to perform Administrative Tasks for the System Database?
Question
What are the minimum permissions to perform Administrative Tasks for the BarTender System Database in BarTender 2016 and later?
Answer
Server Role: dbcreator and Database Role: db_owner are the minimal required permissions to perform every administrative task available under the Administration Console for the BarTender System Database.
Here is a breakdown of which permissions match which task in the Administration Console:
Action/Procedure | Minimum Permissions |
View database size | Database Permission: View Database State |
Backup Database | Database Permission: Backup Database The directory where the backup is saved needs to exist, and the SQL Server Service needs to have read/write permission to the directory. |
Restore Database | Server Role: dbcreator Database Role: db_owner |
Run Maintenance now | Database Permissions: Connect, delete, execute, insert,select, update, backup database (if "Archive deleted records" checkbox is enabled) |
Purge All Records Now | Database Permissions: Connect, delete, execute, insert,select, update, backup database (if "Archive deleted records" checkbox is enabled) Database Role: db_ddladmin |
However, even with the right set of permissions, there is currently a known problem which will make running Maintenance fail unless the user running the action has a sysadmin role. The error message will be similar to the below:
Stored Procedure: sp_updatestats Failed; Inner Message: User does not have permission to perform this action.
Processed 584 pages for database 'SystemDB', file 'SystemDB' on file 1.
Processed 1 pages for database 'SystemDB', file 'SystemDB_log' on file 1.
BACKUP DATABASE successfully processed 585 pages in 0.091 seconds (50.217 MB/sec).
This is a known SQL Server problem. One of the stored procedures used in maintenance, 'SpDeleteRecords', calls a SQL Server "builtin" procedure named, 'sp_updatestats'. This is to help inprove query performance once the records have been deleted.
Unfortunately, even though Microsoft's documentation states that the database owner (dbo) has permission to run this, there's currently a bug in SQL Server which makes it fail.
A workaround for customers is to have them alter the 'SpDeleteRecords' stored procedure (via SQL Server Management Studio) as follows:
Generate an ALTER script for 'SpDeleteRecords' by right-clicking it in the left-hand pane of SQL Server Management Studio under "YourDatabase\Programmability\Stored Procedures", right-clicking on dbo.SpDeleteRecords and selecting "Script Stored Procedure as ALTER to > New Query Editor Window".
Append "with execute as 'dbo'" to the ALTER line of the script as follows:
ALTER PROC [dbo].[SpDeleteRecords](@pastUtcTicks bigint, @categories nvarchar(1024)) with execute as 'dbo'
Run this script with F5 or the toolbar's "! Execute" button.
This will force 'SpDeleteRecords' to run under the credentials of the database owner. After you have modified the Stored Procedure, try running maintenance again (with the minimal permissions mentioned in the above table) and it should work.
More Information (Internal Use Only)
See DEVQ-4363 and BUG-2270
Also, sooner or later, we will probably get complaints about why a certain server role or database permission needs to be granted, and that this will pose a security risk (such as db_owner). In fact, I got this question from a customer today, and this is what I answered
Rather than looking at this from the point of what database role this SQL Server user has, I would approach this from the point of view of:
- Is the password for the SQL Server account used by the BarTender System database a complex and therefore more secure password?
- What member of your company knows / has access to this password.
The db_owner database permission is already a security concern if you would ask a SQL Server administrator, but I think that it mostly matters who has access to the password for this SQL Server account, and how complex the password is.