I’m not sysadmin, I just play one on TV

I really like the feature known as “All Permissions Grantable” in which any permission can be managed using the GRANT verb, because there are no “magic” logins or users anymore. The coolest thing is that all server roles are now defined in terms of the permissions they have; this chart is in the BOL. In it, sysadmin equates to CONTROL SERVER.

But I did notice when reading through BOL that many things are defined as “this can only be done by sysadmin”. But what if I’m not sysadmin and I do have CONTROL SERVER? Can I do those “sysadmin only“ things? Or the collolary: if I am granted CONTROL SERVER, do I become sysadmin (that is, show up in the system tables as sysadmin).

At least with the first three items I tried, I can perform the “sysadmin only” function but I’m not sysadmin. Good thing to remember when looking through an instance for high privilege users, to make sure some logins don’t slip through the cracks. Here’s example:

create login bob with password = ‘StrongPW1’

grant control server to bob

— login as bob or
execute as login=’bob’
create database sample
— this works for bob
alter database sample set trustworthy on

— bob is not a member of sysadmin server role
— BOL says he must be
sp_helpsrvrolemember ‘sysadmin’

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.