Which Server Permissions Do You Need To Use SQL CoPilot?
SQL CoPilot performs in-depth checks, for which membership of the sysadmin role is required.
However, if you have lesser permissions SQL CoPilot will still work and provide the information available to your level of access.
On startup, a check is made and a message is displayed if your permissions are insufficient.
The minimum permissions required are:
- VIEW SERVER STATE
- VIEW ANY DEFINITION
With just these permissions, and no others, you will be able to view the majority of SQL CoPilot dashboards. To use further functionality some additional permissions are required. Note: if you are a member of the sysadmin role you can ignore this.
The minimum permissions allow you to view the list of databases and the overview for each database.
To be able to view more database information, including all the index analysis, you also need CONNECT permissions to the database you want to examine. This can be achieved by creating a user in the database. It is not necessary to give the user any additional permissions.
Reading from the default trace requires first reading from sys.traces. You need the ALTER TRACE permission to do this.
To view the error log you need EXECUTE permission on master.dbo.xp_readerrorlog.
SQL Agent Jobs
To use the Jobs reports you need EXECUTE permission on master.dbo.xp_sqlagent_enum_jobs and SELECT permission on msdb.dbo.sysjobs, msdb.dbo.sysjobhistory, msdb.dbo.sysjobactivity and msdb.dbo.syscategories. You must also be a member of the SQLAgentReaderRole database role in msdb.
Functionality Requiring System Administrator
There are three checks performed by SQL CoPilot that need membership of the sysadmin server role.
- The last time CHECKDB ran against a database (this calls DBCC DBINFO);
- The number of virtual log files in a database (this calls DBCC LOGINFO);
- The objects waiting on latches, in the waiting tasks report (this calls DBCC PAGE);
This script will give you all the permissions mentioned above (apart from sysadmin).
-- This assumes a login called cp
-- Replace this with the Windows account you use to run SQL CoPilot (or the SQL login you configure it with if not using Windows authentication)
-- Minimum server level permissions
GRANT VIEW SERVER STATE TO [cp];
GRANT VIEW ANY DEFINITION TO [cp];
-- Optional server level permissions
GRANT ALTER TRACE TO [cp];
-- Optional permissions in master
CREATE USER cp FOR LOGIN cp;
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO [cp];
GRANT EXECUTE ON xp_readerrorlog TO [cp];
-- Optional permissions in msdb
CREATE USER cp FOR LOGIN cp;
GRANT SELECT ON sysjobs TO [cp];
GRANT SELECT ON sysjobhistory TO [cp];
GRANT SELECT ON sysjobactivity TO [cp];
GRANT SELECT ON syscategories TO [cp];
EXEC sp_addrolemember N'SQLAgentReaderRole', N'cp'
-- Optional permissions in all databases
EXEC sp_MSforeachdb 'use [?]; CREATE USER cp FOR LOGIN cp;';