Sql-server – Why securityAdmin does not have enough permission

permissionsSecuritysql serversql-server-2012

I am a server securityAdmin.

enter image description here

The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things

  1. make myself as sysAdmin
  2. grant myself write right to a database. (currently in db_denydatawriter role )

enter image description here

enter image description here

Did I miss anything?

Thanks

UPDATE

per RDFozz's comment, I was able to add a new 'dummy' login, but failed to set this login as a user of this database.

enter image description here

UPDATE 2 — final script that works!

sepupic's answer is great! following his instruction, here is the SQL.

first, use myLogin to create a new super_user.

CREATE LOGIN [super_login] WITH PASSWORD=N'yourPassword'
GRANT CONTROL SERVER TO [super_login]
Grant IMPERSONATE ON LOGIN::[sa] TO [super_login]

then login with the super_login, run this. 

Execute as login = 'sa'
  ALTER ROLE [db_denydatawriter] DROP MEMBER [mylogin]
  --ALTER ROLE [db_owner] add MEMBER [mylogin]
  ALTER server ROLE [sysadmin] add MEMBER [mylogin]
Revert

Best Answer

The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things

  1. make myself as sysAdmin
  2. grant myself write right to a database. (currently in db_datadenywrite role )

All the above is wrong. Secutiry admin is not sysadmin, if it was so for what purpose 2 different server roles were created?

Security admin has these 3 permissions at server level:

  • CONNECT SQL
  • ALTER ANY LOGIN
  • VIEW ANY DATABASE

CONNECT SQL and VIEW ANY DATABASE permissions are granted to public, so there is only one additional permission, ALTER ANY LOGIN.

At the database level this role adds you the possibility to grant permissions to other users, but you don't have alter any user / alter any role, so you cannot add yourself or any other user to database role.

The phrase

The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

in my opinion means the following: security admin can elevate his permissions to sysadmin in some way.

I can give you an idea of this "way".

If you are security admin you can create SQL Server login ( I'll call him super_login) and grant him CONTROL SERVER permission.

The next step is to log in as super_login and give all the permissions you want to your login. You can make yourself db_owner of the database where now you are db_datadenywriter, or drop yourself from db_datadenywriter or merely add your login to sysadmin fixed server role.

Related Question