Why you shouldn’t use the securityadmin role anymore

Hi there!

I usually avoid using the two “radical” words: “never” and “always” but regarding the membership need for the “securityadmin” server role since SQL 2005/2008 onward, I find it hard to come up with a good reason why it should ever be used, especially considering the security risks involved. A few weeks ago, while checking permissions for some logins in a (critical) SQL Server 2019 instance, I came across some really worrying situations, among which I found this one… again! Although it wasn’t the worst I found there (the worst was to bad to even mention here) I felt impelled to write about this one, maybe because the risks may not be so obvious or are somewhat concealed… Well, let’s bring them to light! 🙂

The securityadmin fixed server role is very old and it allows to manage logins and their properties. By itself this could already cause some disruption (imagine if one drops or disables a sysadmin group, or disables critical logins by mistake), but other than that, it’s true that a member of securityadmin doesn’t have access to any database, nor can he do almost nothing beyond dealing with logins, right? Actually this is a wrong and most dangerous assumption!

Applications usually deal with users access/management and although there are many ways to implement this, in some cases, the possibility of using Logins directly mapped to SQL Server is considered. In those cases, one or more users can be added to the securityadmin fixed server role, in order to manage logins as required by the application. I must say that I’m not in favor of this approach but that’s not the point here. Let’s imagine this scenario or any other scenario that would lead to having one user as a member of the securityadmin role and see the risks.

This post will be demo based, so you can try it if you feel like it 🙂

First I’ll create the login and make it a member of the securityadmin fixed server role:

CREATE LOGIN MisterLoginManager WITH PASSWORD = 'L0-UprR^Gne)Hasg'

ALTER SERVER ROLE securityadmin ADD MEMBER MisterLoginManager

Let’s start by checking the effective permissions for this login. I’ll connect as “MisterLoginManager” and run the query below:

SELECT SUSER_SNAME() as who_am_i, * FROM sys.fn_my_permissions(NULL, NULL)

The effective permission that allows to manage the logins is “ALTER ANY LOGIN“. As we can see, this is the only additional effective permission that differs from a login without any server role membership (other than public) nor privileges.

Ok, so let’s see a few things that we can do, using this login:

--Create a new login:
CREATE LOGIN my_application_usr1 WITH PASSWORD = 'L0-UprR^Gne)Hasg1'

--Disable or enable the login:
ALTER LOGIN my_application_usr1 DISABLE

ALTER LOGIN my_application_usr1 ENABLE

--Reseting the password:
ALTER LOGIN my_application_usr1 WITH PASSWORD = 'new_password_L0-UprR^Gne)Hasg'

--Droping the login:
DROP LOGIN my_application_usr1

Good so I can do the stuff related with login management in SQL Server. I can “view any database” also. Does that mean that I have access to them? Let’s see:

No luck here, what about DMVs? Let’s try a few:

I can only see my own session.

Hum, it seems that I can’t do anything besides creating, dropping and managing the logins… So, why was I so worried after all? Oh yes, because there is another thing that wasn’t listed in the permissions (but it’s known and documented here) that will turn this apparently “safe” role into something that can become completely unsafe. Any member of this role can also GRANT, DENY, and REVOKE server-level permissions!

So now let’s have some fun, while still acting as “MisterLoginManager”, I’ll now create a new login called “TheDarkSideLogin“:

CREATE LOGIN TheDarkSideLogin WITH PASSWORD = 'L0-UprR^Gne)Hasg'

And now, I’ll grant TheDarkSideLogin all the power at server level. Yes, it will become a sysadmin. This cannot be done in a single step like this:

No Sir, “MisterLoginManager” can’t add any login (directly) to sysadmin. So, let’s do this in 3 little steps.

Step 1 – GRANT Control to TheDarkSideLogin:

GRANT CONTROL SERVER TO TheDarkSideLogin

Here is the major vulnerability of belonging to the securityadmin server role and so easy to explore, yes by means of the GRANT above, the TheDarkSideLogin already has huge power over the “poor” SQL Server Instance but isn’t yet a sysadmin and it’s true that some tasks can only be carried out by a sysadmin. The thing is that TheDarkSideLogin can now impersonate any other login, such as “sa”, which leads to the next step.

Step 2 – Login as TheDarkSideLogin:

When I login as TheDarkSideLogin, I can see that it’s possible to impersonate any login:

Step 3: Become a sysadmin via impersonation:

All TheDarkSideLogin has to do now is execute the next statements to become a sysadmin

EXECUTE AS LOGIN = 'sa'
SELECT SUSER_SNAME() as who_am_i

ALTER SERVER ROLE sysadmin ADD MEMBER TheDarkSideLogin

SELECT IS_SRVROLEMEMBER('sysadmin', 'TheDarkSideLogin') as is_sysadmin

As we can see, TheDarkSideLogin successfully added himself to sysadmin on behalf of “sa”:

And now the TheDarkSideLogin is a sysadmin with full power over the entire SQL Server Instance. Imagine an application allowing to inject some T-SQL code… this can become a real mess!

How to avoid this situation?

First of all, ask yourself if you really need to implement this kind of permission in your environment, i.e. having someone (other than the DBAs) manage the logins in SQL Server. If you don’t, then don’t add anyone, instead, remove every membership you find in the securityadmin role and problem solved!

But if you actually need to implement this type of solution for some reason then prior to SQL Server 2022, instead of adding a login to the securityadmin server role, just grant those logins the permission “ALTER ANY LOGIN“, like this:

--In this case I'll start by removing MisterLoginManager from the securityadmin role, since it was already a member
ALTER SERVER ROLE securityadmin DROP MEMBER MisterLoginManager

--And now I'll grant the permission and only the permission:
GRANT ALTER ANY LOGIN TO MisterLoginManager

Now MisterLoginManager can still manage logins in SQL Server but he can no longer use GRANT, which was the cause of the greatest vulnerability, as showed above. Now that won’t work anymore:

Problem solved! Great!

Since SQL Server 2022 there is a new fixed server role called ##MS_LoginManager##. This role allows the same thing as granting the “Alter Any Login” permission to a login manager and also without the possibility of letting its members use GRANT, so from SQL Server 2022 onward, you can either use one way or the other.

If you are in the DBA or cybersecurity team or somewhat responsible for checking security compliance issues, I encourage you to take a tour on your SQL Server instances and look for “securityadmin” members, you never know what you may find, or maybe you do…

That’s it for today, hope you could find it useful.

Cheers!