35,145 Metrics install automatically if you have Redgate Monitor installed.
If you are using Redgate’s SQL Server monitoring tool, Redgate 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
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
