Be careful with securityadmin

Over the last week, I’ve seen two discussions where the securityadmin server role was suggested.

The scenario is typically that we don’t want to make this person a sysadmin, but we want this to manage logins and stuff like that.

The problem, however is that as securityadmin you can create a login and then grant CONTROL SERVER permissions to that login. And having CONTROL SERVER is pretty close to being sysadmin.

First the senior DBA does below:

--The senior DBA, sysadmin, does this
CREATE LOGIN theSecAdmin WITH PASSWORD = 'thePassword'

ALTER SERVER ROLE securityadmin ADD MEMBER theSecAdmin

Then theSecAdmin does:

--Succeeds
CREATE LOGIN theSecAdmin2 WITH PASSWORD = 'thePassword'

--Succeeds!!!
GRANT CONTROL SERVER TO theSecAdmin2

Now theSecAdmin2 can do pretty much anything in the SQL Server, including use all database and whatnot.

There *are* some important differences between being a sysadmin and having CONTROL SERVER, but I’ll save that for another day.

Leave a Reply

Your email address will not be published.