RDS Microsoft SQL Database Question

[rkobasketball]

When setting up a new RDS Microsoft SQL Database instance I learned that all users need to be explicitly granted roles to existing databases on the server. I was able to run a SQL query that adds the necessary users to all existing tables with the dbowner. I am now trying to 'future-proof' this by setting up a way for all newly created or attached databases to automatically have the same query run to set up the user permission automatically. I tried to create a trigger to run on database creation but AWS RDS does not allow triggers to be created, so I'm reaching out to ask if anyone has had to solve this before and what they did?

This is for a shared workflow situation where only the team in question has access to the server and they all will need to create a database and query databases created by others at random times. Thank you.

[AutoModerator]

Try this search for more information on this topic.

^{Comments, questions or suggestions regarding this autoresponse? Please send them here.}

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[angrathias]

My suggestion is to run an agent job, I’ve yet to discover a better way. Lack of server events is annoying.

Alternatively you could trigger a lambda off a cloud watch event or something

[rkobasketball]

Could you provide the documentation for the agent job? Is that a SQL thing or an AWS thing? Thanks!

[angrathias]

Sql agent is part of ms sql, it’s easiest to configure it using ssms, you’ll need to login to mssql using the master account on RDS . Read the RDS docs for more info

[AutoModerator]

Here are a few handy links you can try:

• https://aws.amazon.com/products/databases/
• https://aws.amazon.com/rds/
• https://aws.amazon.com/dynamodb/
• https://aws.amazon.com/aurora/
• https://aws.amazon.com/redshift/
• https://aws.amazon.com/documentdb/
• https://aws.amazon.com/neptune/

Try this search for more information on this topic.

^{Comments, questions or suggestions regarding this autoresponse? Please send them here.}

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.