Give the dbcreator, public, and Bulkadmin roles to a SQL Server user on RDS

Question

I have created a new SQL user on an RDS (Not custom) database with :

`USE master;
GO

CREATE LOGIN my_user WITH PASSWORD = 'my_password';
GO`

And now I want to assign roles to them (dbcreator, public, and Bulkadmin roles).

I tried using 
`EXEC sp_addrolemember 'dbcreator', 'my_user';`

but I received an error message saying 'User or role 'my_user' does not exist in this database'.

How can i resolve this ?

Answer

As [documented ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureSupport.UnsupportedRoles) bulkadmin and dbcreator server roles are not supported in Amazon RDS for SQL Server.

To give permissions to a new user to create databases you can execute

```
GRANT CREATE ANY DATABASE TO [my_user];
```
The user will become database owner for any new database they are creating and will be able to drop the database.

To allow the user to delete backup history on drop, you need to grant permissions on `sp_delete_database_backuphistory`.

```
USE [msdb]
CREATE USER [my_user] FOR LOGIN [my_user] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT EXECUTE ON [dbo].[sp_delete_database_backuphistory] TO [my_user]
```

Hope this helps.

Answer

Thanks for your help !

Is it possible to have an alternative and create an RDS database with default language = French instead of having English as the default language?

Give the dbcreator, public, and Bulkadmin roles to a SQL Server user on RDS

相關內容