SQL Monitor 9

Permissions

These are the minimum permissions required to run SQL Monitor and monitor your servers. To test the permissions, see  Testing data collection methods

If you want to access SQL Monitor through a firewall, check the firewall requirements.

Web service account

  • The account should have Log on as service rights.  For information on how to assign these permissions, see Add the Log on as a service right to an account.
  • The account should have Full Control over the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Monitor. For Vista and Windows 7: C:\ProgramData\Red Gate\SQL Monitor.
  • The account should have Full Control over the folder C:\Documents and Settings\All Users\Application Data\Red Gate\Logs\SQL Monitor or equivalent location.
  • The account should be able to reserve the port the web service is configured to use.  If the user is not a local admin, run the following substituting the correct values for your installation:
	       netsh http add urlacl url=http://*:8080/ user=yourdomain\youruser
The SQL Monitor Web Service is not installed if you use IIS as your Web Server.

Monitoring service account

  • The account should have Log on as service rights. For information on how to assign these permissions, see Add the Log on as a service right to an account.
  • The account should have Full Control over the folder C:\Documents and Settings\All Users\Application Data\Red Gate\Logs\SQL Monitor. For Vista and Windows 7 : C:\ProgramData\Red Gate\Logs\SQL Monitor.
  • The login should be a member of the db_owner database role on the Data Repository database (called RedGateMonitor by default).

Monitoring host Windows machines

  • The simplest way to do this is to make the account an administrator on the remote Windows host machine. If this isn't possible, see Monitoring host Windows machines without admin permissions.
  • The account should have login rights locally on the machine where the base monitor is installed, so that SQL Monitor can authenticate the account.

Monitoring host Windows machines without admin permissions

  • The account should have Remote AccessRemote Launch and Remote Activation permissions for DCOM on the target machine. For information on how to assign these permissions, see Securing a Remote WMI Connection.
  • The account should have Remote Enable and Enable Account permissions for the WMI namespaces CIMV2, Root and MSCluster (if it exists on the machine). This can be done from WMI Control in Computer Management.
  • The account should be a member of the Performance Monitor Users group on the target machine.
  • The account should have Create Child (CC)List Children (LC)Read Property (RP) and Read Control (RC) permissions for scmanager on the target machine. This can be done by adding (A;;CCLCRPRC;;;<sid>) to scmanager’s security descriptor, where <sid> is the SID of the account. An example of how this is done is shown below.

    sc sdset scmanager "D:(A;;CC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)(A;;CCLCRPRC;;;<sid>)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)"

    The SID can be obtained by running

    wmic useraccount where name='<account_name>' get sid
    
  • The account should have List Children (LC) permissions on all SQL Server services on the target machine. This can be done by adding (A;;LC;;;<sid>) to the security descriptor of each service, where <sid> is the SID of the account. An example of how this is done is shown below.

    sc sdset MSSQL$SQL2012CS "D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)(A;;LC;;;<sid>)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)"

Monitoring SQL Server instances

The account used to monitor your SQL Server instance should have the following permissions:

  • member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to turn on the deadlock trace flag).
If you are unable to grant sysadmin permissions to the account, please follow the steps in this article and grant the following permissions:
  • member of the db_datareader role on the msdb system database.
  • member of SQLAgentReader role on the msdb system database.
  • 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).
  • VIEW ANY DEFINITION server permission.
  • ALTER TRACE server permission (if you want to enable trace data).
  • VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  • member of the db_owner  role on the tempdb database.
  • EXECUTE on xp_readerrorlog.

Monitoring Azure SQL Servers

  • The account used to monitor your Azure SQL Server must be the server admin account used to create the Azure SQL Server.

Didn't find what you were looking for?