0

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
  • The link you included specifies that the reason securityadmin should be treated like sysadmin is that securityadmin can grant access to the server, and to certain roles with respect to databases. So, depending on the permissions granted the securityadmin user, they might (for instance) be able to create a new user, and grant that user dbo access to a database. This does not mean securityadmins can do everything sysadmins can do. – RDFozz Mar 5 '18 at 23:18
  • thanks @RDFozz, I created a new login, but failed to add it as a db's user. (I was able to add it to any other databases). – Rm558 Mar 6 '18 at 0:03
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.