Trigger on RDS SQL server instance

Question

We have an RDS instance with DB engine SQL Server Standard Edition. Using SQL server db, we are trying to create a trigger but it is giving error as You do not have the SUPER privilege.

Can you guide on how can we get that access or is there any other way to have a trigger created in DB?

Answer

To create triggers in an RDS for SQL Server database, the database user will need certain privileges. Since RDS manages the underlying infrastructure, some advanced privileges like SUPER are restricted.

Some options to consider:

Grant the database user privileges like VIEW DEFINITION, CREATE PROCEDURE etc. This can be done by first creating a database user, then granting the required privileges.

CREATE LOGIN username WITH PASSWORD 'password';

USE database;
CREATE USER username FOR LOGIN username;

GRANT VIEW DEFINITION TO username; 
GRANT CREATE PROCEDURE TO username;

Consider using stored procedures instead of triggers, if the use case allows. Stored procedures can achieve similar functionality without requiring advanced privileges.

The options may be limited since RDS is a managed service.

Trigger on RDS SQL server instance

Relevant content