AWS RDS - MsSql - Create schema, user or role in “master” or “msdb” databases

0
votes

I'm using AWS RDS MsSql express V13. I'm trying to create a schema, user and role using the root user I created during the RDS setup but can't. I'm using SQL client.

for example:

use [master]
CREATE USER [my_user] FOR LOGIN [my_login] WITH DEFAULT_SCHEMA=[dbo]

returns: "User does not have permission to perform this action."

1
sqlsql-serveramazon-web-servicesamazon-rds

1 Answers

2
votes

AWS RDS restricts permissions on the master database, so you can't create schemas, roles, users, or logins there.

You need to create a second database, which will then allow you to create schemas, users, and roles as expected.

-- use the master database to create your new database:
USE master;  
CREATE DATABASE db_test;

-- then, use your new database to create your schema, login, and user:
USE db_test;
CREATE SCHEMA yourschema;
CREATE LOGIN [youruser] WITH PASSWORD = '<PASSWORD>';
CREATE USER [youruser] FOR LOGIN [youruser] WITH DEFAULT_SCHEMA = [yourschema];