Principals with sysadmin login

This metric counts the number of principals who are members of the sysadmin fixed server role. Adding a normal user to this role could pose a security risk and is not recommended.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

This metric counts the number of principals who are members of the sysadmin fixed server role. SQL Server relies on role-based security to manage permissions. If multiple IT system administrators have permissions to set up new SQL Server logins, they might be inclined to do so as part of the sysadmin role. Adding a normal user to the sysadmin role could pose a security risk and is not recommended unless the principal is highly trusted. See Server and Database Roles in SQL Server (MSDN).

The associated alert will be raised if the number of expected principals goes higher than the threshold specified. This is useful if your organizations doesn’t have the ability to use Audit Specifications or write triggers for new user creation.

If you want to set a different threshold for each SQL Server you’re monitoring, install the metric and follow the instructions on Customizing settings in Configuring alerts (SQL Monitor documentation).

Metric definition

Name

Principals with sysadmin login

Description

This metric counts the number of principals who are members of the sysadmin fixed server role. SQL Server relies on role-based security to manage permissions. If multiple IT system administrators have permissions to set up new SQL Server logins, they might be inclined to do so as part of the sysadmin role. Adding a normal user to the sysadmin role could pose a security risk and is not recommended unless the principal is highly trusted. If this count is higher than expected, contact system administrators to find out which principals were recently given access and why.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

60

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Principals with sysadmin login increased

Description

This alert is raised when the number of principals who are members of the sysadmin fixed server role goes above the threshold specified. To set a different threshold for each SQL Server you're monitoring, install the metric and then follow the instructions on Customizing settings in Configuring alerts.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:According to your environment
Medium:Disabled
Low:Disabled

Note: To set a different threshold for each SQL Server you're monitoring, install the metric and then follow the instructions on Customizing settings in Configuring alerts (SQL Monitor documentation).

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled