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.