Principals with sysadmin login

| 4 Comments | 8,865 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 3.8/5 (5 votes cast)

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).

Principals with sysadmin login, 3.8 out of 5 based on 5 ratings

Metric definition

Metric 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.
Enter the T-SQL query that will collect data:
SELECT COUNT(spm.name)
FROM sys.server_role_members srm, sys.server_principals sp, sys.server_principals spm WHERE sp.name = 'sysadmin'
AND sp.principal_id = srm.role_principal_id AND spm.principal_id = srm.member_principal_id;
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every minute
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 3.8/5 (5 votes cast)
  • Tony

    This would be more useful if it interrogated server principals and in case of windows groups, included the number of members in that group and members in any nested groups.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • David Heath

      Hi, it’s not really meant for giving detailed information on the server principals. The members of the fixed server role shouldn’t change very much once the SQL Server is configured. This metric will alert if someone adds a login to the sysadmin service principal role, which could cause a security risk. The fixed server role filter could be changed to any of the fixed server roles and metrics configured on all of them if required.

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
  • Stevan Allen

    We have found a SQL account with sysadmin rights, is there anyway of determining who elevated the account and when they did it?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • David Heath

      You can query the table sys.server_principals which will show the creation date of the account and the last modified date. If the last modification of the account was to elevate it, then this should show when this happened. I don’t know of any way to find who modified the account. I would suggest adding a trigger to fire/email when a new account is added or amended by someone so that there is a paper trail for these events. You could also use other tools to do this such as Server Auditing.

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)