I am a server securityAdmin.
The securityAdmin role should be treated as equivalent to the sysadmin role, according to Microsoft, but I failed to do below 2 things
- make myself as sysAdmin
- grant myself write right to a database. (currently in db_denydatawriter role )
Did I miss anything?
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.
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