Who’s In Your Fixed Server Roles?

calendar May 8, 2015 · Security SQL Server  ·
Share on: linkedin copy

It's 10PM*. Do you know who your sysadmin role members are?

SQL Server's fixed server roles control incredibly broad permissions across your entire instance. For this reason, membership in these roles should be granted carefully and reviewed often. Fortunately, reviewing role membership is extremely easy, so you can always answer the above question better than Homer Simpson.

The Easy Way: SSMS GUI

In the SSMS Object Explorer, open the "Security" folder for an instance, and then open "Server Roles".

You'll see it displays the 9 fixed server roles. Double-clicking on any role brings up a dialog that shows all its members:

If you hate typing, then this method is for you. Otherwise, you'll probably like

The Easier, Faster Way: T-SQL

In SQL Server, where there's a will, there's a way to do something in T-SQL. And it will probably be better/faster/cheaper too. Here's the query I use to check server role memberships:

1SELECT
2   rp.name AS Server_Role,
3   mp.name AS Role_Member,
4   mp.type_desc AS Member_Type,
5   mp.is_disabled AS Login_Is_Disabled
6FROM sys.server_role_members srm
7INNER JOIN sys.server_principals rp ON srm.role_principal_id = rp.principal_id
8INNER JOIN sys.server_principals mp ON srm.member_principal_id = mp.principal_id;

On my test server here at home, it returns the following results:

You can see the 4 columns it gives us: the name of the server-level role, the name of the login, what type of login it is, and whether or not the login is disabled. The SSMS role member dialog shown above gives no indication if a login is disabled or not, so I find that column especially handy in the query.

It's important to note that this query returns members of all server-level roles at once. This example shows only the members of the sysadmin role because it's the only one with members on this instance.

What to look for

  • First and foremost, do you recognize all the members of all roles, and should they have that level of permissions?
  • You'll most likely see windows groups included in this list as well. Do all members of the group belong there?
  • You cannot remove the sa login from the sysadmin role, so I recommend either disabling it or changing its name.

Securityadmin: the most dangerous role

Ask anyone familiar with SQL Server what the most dangerous role is, and they'll probably say "sysadmin." While sysadmin has the keys to the kingdom in the sense that its members can do absolutely anything on the server, there is something securityadmin has that sysadmin doesn't: secrecy.

Members of the securityadmin role can manage any login on the server, including membership in server level roles. This means someone with securityadmin rights can:

  • add themselves to the sysadmin role
  • do whatever dastardly deeds they want
  • and then remove themselves from from sysadmin

If you're only looking at members of the sysadmin role, you will be none the wiser. When checking server-level roles, remember that securityadmin is every bit as powerful and dangerous as sysadmin. It just might save your bacon!

*It's always 10PM somewhere