Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to drop login on AWS RDS SQL Server without sysadmin using Hashicorp Vault

12 May,2022 by Tom Collins

Question: I've read through these two posts - AWS-RDS SQL Server limitations   & A simple guide to Hashicorp and SQL Server Secrets Engine .    One of the statements in the Hashicorp article is confusing -

"The MSSQL plugin supports databases running on  AWS RDS, but there are differences that need to be accommodated. A key limitation is that Amazon RDS doesn't support the "sysadmin" role, which is used by default during Vault's revocation process for MSSQL"


Answer:     This does come across as confusing . Firstly - the Hashicorp revocation process is not an issue if the vaultuser i.e the login used by Hashicorp to connect to the SQL Server has sysadmin role.

But as noted AWS RDS SQL Server by default  does not allow sysadmin for any  user logins. If you attempt to create a login with sysadmin privileges a message will appear


Msg 15151, Level 16, State 1, Line 20
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.


A  Hashicorp workaround is required - which basically means - create a  a role with custom revoke statements:


vault write database/roles/my-role revocation_statements="\
USE my_database; \
(SELECT name \
FROM sys.database_principals \
WHERE name = N'{{name}}') \
DROP USER [{{name}}] \

(SELECT name \
FROM master.sys.server_principals \
WHERE name = N'{{name}}') \
DROP LOGIN [{{name}}] \


This will work if you are using a root vault account that does not have sysadmin privileges. 

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to drop login on AWS RDS SQL Server without sysadmin using Hashicorp Vault | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer