Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Marlon Ribunal - SQL, Code, Coffee, etc.

I'm passionate about SQL Server. But I feel like I haven't reached my full potential yet. So, this is my mission: My purpose is to help people in their pursuit of growth and development; and, thereby, help myself realize my full potential as a professional, husband, father, christian, and human being.
My online presences include: Tech Blog: Marlon Ribunal - SQL, Code, Coffee, etc. Productivity & GTD Hack Blog: Productivity Bits Twitter: @MarlonRibunal

Who are the sysadmins in this sql server?

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:

TSQL To Find Logins With Sysadmin Fixed Server Role In SQL Server 2008 2012

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:

System Stored Procedure sp_helpsrvrolemember to return logins with certain role in sql server

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:

Listing all the server roles in SQL Server

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:

who are the domain members of the Windows Group in my SQL Server

No related posts.

Comments

Leave a comment on the original post [marlonribunal.com, opens in a new window]

Loading comments...