Who Has sysadmin Access to your SQL Servers?

Phishing attacks account for more than 80% of all security incidents according to this CSO article Top cybersecurity facts, figures and statistics. And the resulting data breaches cost an average of $3.92 million. With security incidents and data breaches making the news daily, it’s important to secure your networks, including your SQL Servers. That’s not a trivial task. But a good place to start is knowing who has sysadmin access to your SQL Servers.

What can a sysadmin do?

We often talk about a sysadmin as if it refers to an individual, but sysadmin is actually a role. Or more accurately, sysadmin is a Fixed Server Role. A role is a group or collection of individual logins. We assign permissions to the role and all members of the role inherit the permissions of the role. You know, role-based security. It’s much easier to manage than assigning permissions to the individual logins.

Members of the sysadmin role can do anything in SQL Server. Anything is a pretty bold statement. But in this case, it’s true. They have the proverbial keys to the kingdom.

Someone with sysadmin privileges can create and drop databases. They can grant or revoke permissions for other logins. They can change the configuration of the SQL Server; for example, the can enable xp_cmdshell or CLR integration. They can query or change data. They can do it all.

So, if someone in your organization who’s a member of sysadmin falls for a phishing attack, it’s bad. The attacker very likely has sysadmin privileges on your SQL Server. Yikes! And if they know what they are doing, they very well may be able to go beyond the SQL Server.

Who are your sysadmins?

Knowing who has sysadmin privileges to your SQL Servers is an important first step in securing the servers. Here’s a query I use to get a list of syadmins in SQL Server.

--who are the sysadmins for this SQL Server? 
SELECT p.name AS [loginname],
    p.type,
    p.type_desc,
    CONVERT(VARCHAR(10), p.create_date, 101) AS [created],
    CONVERT(VARCHAR(10), p.modify_date, 101) AS [updated],
    p.is_disabled,
    CASE COALESCE(sl.is_expiration_checked, -1)
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE '--'
        END AS [is_expiration_checked],
    CASE COALESCE(sl.is_policy_checked, -1)
        WHEN 0 THEN 'No'
        WHEN 1 THEN 'Yes'
        ELSE '--'
        END  AS [is_policy_checked],
    LOGINPROPERTY(sl.name, 'PasswordLastSetTime') AS PasswordLastSetTime
FROM sys.server_principals AS p
    JOIN sys.syslogins AS s ON p.sid = s.sid
    LEFT JOIN sys.sql_logins AS sl ON sl.sid = p.sid
WHERE p.type_desc IN('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    -- Logins that are not process logins
    AND p.name NOT LIKE '##%'
    AND p.name NOT LIKE 'NT SERVICE%'
    -- Logins that are sysadmins
    AND s.sysadmin = 1 ;

Notice that the script looks for SQL logins as well as Windows logins. For the SQL logins, the script looks to see if the login must adhere to password expiration and complexity requirements. It also shows the last time that the SQL login’s password was changed.

The results from a test lab are shown below.

How many sysadmins should you have?

So, you’ve run the query and gotten the list of sysadmins. You’ve likely found some surprises. Maybe there’s a login for someone that left the company years ago. Or maybe a vender was temporarily granted sysadmin privileges during an installation but it was never removed. Some power users or developers may have been added to the role for a reason no one can remember? And your CFO needs sysadmin access? Really?

In any case, there are more members of sysadmin than there should be. Identify those logins and remove them. Grant them lower level permissions that will meet their needs while still protecting your SQL Server.

But this begs the question – how many sysadmins should you have?

That’s going to depend on your environment. The size of your company and the number of SQL Servers you have will certainly influence the number of sysadmins you need. I cannot answer that question for you, at least not with a specific number.

What I can say is: you should have as few as possible, but no less than that.

Here are a few other posts that you may find interesting.

• What Takes Precedent db_datareader (GRANT) or db_denydatareader (DENY)?
• Scary and Dangerous Things in SQL Server
• Who’s the SQL Server Database Owner and How Can You Change It?
• Securing Your SQL Servers, What Should You Audit?
• Protect Your SQL Server from MrbMiner and Other Malware Attacks

Also, check out our Script Library.

Want to work with The SERO Group?

Worried about your SQL Server’s security? Want a second set of eyes to review it? We can help.

Schedule a call with us to get started. It’s easy and there is no obligation.