Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a complete control of your server. So, it is very important to know who these people are.

Just to give you an example on why it is very important to check who’s who in your server from time to time, Team Shatter has recently published an advisory on Privilege Escalation Via Internal SQL Injection In RESTORE DATABASE Command.

According to the advisory, there is a vulnerability in the RESTORE DATABASE command that allows malicious users to run SQL codes via internal sql injection. This vulnerability can be exploited by users with CREATE DATABASE permission.

A rogue user can find his way to taking control over your databases by using a backdoor such as the vulnerability described above. Imagine the same user was able to add himself to a server-wide role such as the sysadmin.

So, going back to the query, here is what it looks like:

USE master
GO
SELECT  p.name AS [loginname] ,
        p.type ,
        p.type_desc ,
        p.is_disabled,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1
GO

And that query should give us something like this:

There is a quicker way to do that, though. You’ll achieve a similar result by executing the system stored procedure sp_helpsrvrolemember:

 EXEC sp_helpsrvrolemember 'sysadmin'

And, you’ll get a result that looks like this:

Of course, you can use any of the other server roles with sp_helpsrvrolemember as parameters. For example, if you want to look for users with the database creator role, you use the variable 'dbcreator' instead of 'sysadmin'.

Server roles are listed somewhere in one of the system tables in the master database. But there’s already a system stored procedure for that as well –  sp_helpsrvrole. You don’t have to dig in and search for them yourself. SQL Server gives you most of the stored procedures you’ll likely need out of the box.

 EXEC sp_helpsrvrole

Executing that will give you the following list:

Update from the comments:

Here’s a good one from Eric Russell (see comment below): to return what accounts are members of a domain group, we do this:

 xp_logininfo 'DEVCOMPUTER\SQLAdmins', 'members';

*suppose SQLAdmins is a domain group

And, it does exactly what it is meant to do – returns the members of the group like this:

No related posts.