How well do you know the security in your SQL instances? Do you know who has sysadmin level permissions? SQL Server provides a few methods for you to find out who is a member of which roles at the server level.
For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role. For those that want something a bit faster, you can use a script to return this information for you. Just as with most things TSQL, there are numerous different ways of writing this script. Here are some of those methods.
Verifying Server Role membership
[codesyntax lang=”tsql”]
SELECT SUSER_NAME(SR.role_principal_id) AS ServerRole, SP.name as PrincipalName
FROM sys.server_role_members SR
Inner Join sys.server_principals SP
On SR.member_principal_id = SP.principal_id
Union
Select 'Public' as ServerRole, SP.name as PrincipalName
From sys.server_principals SP
where type in ('u','s','g')[/codesyntax]
With this script, I am querying the sys.server_role_members and sys.server_principals views. For simplicity sake, I am also using the SUSER_NAME() function to derive the role name. Note that I threw in a union all to get back the ‘Public’ group membership. The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.
An Alternative
[codesyntax lang=”tsql”]
SELECT
ServerRole = rp.name,
PrincipalName = SP.name
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
Union
Select 'Public' as ServerRole, SP.name as PrincipalName
From sys.server_principals SP
where type in ('u','s','g')
And is_disabled = 0[/codesyntax]
This one is quite simple as well. Note that I am not employing the use of the SUSER_NAME function but have used another join in its place. I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled. That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.
Another Option
This is really the easiest of the three queries.
[codesyntax lang=”tsql”]
SELECT SUSER_NAME(SR.role_principal_id) AS ServerRole
, SUSER_NAME(SR.member_principal_id) as PrincipalName
FROM sys.server_role_members SR
Union
Select 'Public' as ServerRole, SP.name as PrincipalName
From sys.server_principals SP
where type in ('u','s','g')
And is_disabled = 0[/codesyntax]
I am still employing the union statement to populate the public role. Notice the difference in the first half of the query though. I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view. This is a little easier to follow and write. Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.
All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers). The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server. It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.
