Minimum necessary permissions to monitor SQL server instance – Red Gate SQLMonitor

  1. The account used to monitor your SQL Server instances should have the following permissions:
  2. Member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to tur on the deadlock trace flag.
  3. If you are unable to grant sysadmin permissions to the account. Grant the following permissions:
  4. Member of the db_datareader role on the msdb system database.
  5. Member of the SQL_AgentReader role on the msdb system database.
  6. Member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
  7. VIEW ANY DEFINITION server permission.
  8. ALTER TRACE server permissions (if you want to enable trace data).
  9. VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  10. Member of the db_owner role on the tempdb database.
  11. EXECUTE on xp_readerrorlog.
  12. Below is the script to grant the non-sysadmin permissions described above:
USE [msdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of the db_datareader role on the msdb system database*/
ALTER ROLE [db_datareader] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of SQLAgentReader role on the msdb system database*/
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
/*Member of the db_owner  role on the tempdb database*/
ALTER ROLE [db_owner] ADD MEMBER [Domain\SQLServerAccount]
GO
use [master]
GO
/*ALTER TRACE server permission*/
GRANT ALTER TRACE TO [Domain\SQLServerAccount]
GO
use [master]
GO
/*VIEW ANY DEFINITION server permission*/
GRANT VIEW ANY DEFINITION TO [Domain\SQLServerAccount]
GO
USE master;
GRANT EXEC ON xp_readerrorlog TO [Domain\SQLServerAccount];

/* Run the output of the below script in a separate window in order to grant db_ddladmin role and view database state permission to the user*/
USE [master]
GO
DECLARE @UserName VARCHAR(25) = 'Domain\SQLServerAccount'
SELECT 'USE ['+name+'] CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+']; ALTER ROLE db_ddladmin ADD MEMBER ['+@UserName+']; GRANT VIEW DATABASE STATE TO ['+@UserName+'];'
FROM sys.databases

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: