Help Center/ Relational Database Service/ User Guide/ Working with RDS for SQL Server/ Usage of Stored Procedures/ Granting Database Permissions to Subaccounts
Updated on 2024-10-14 GMT+08:00
View PDF
Share

Granting Database Permissions to Subaccounts

Scenarios

You can use a stored procedure to grant permissions of a custom database to a specified subaccount created by the rdsuser user to make the database visible to the subaccount. If the database permissions are not granted to the subaccount, the subaccount cannot see or perform operations on the database.

Prerequisites

An RDS for SQL Server DB instance has been connected. For details about how to connect to a DB instance, see Connecting to a DB Instance Through a Public Network.

Constraints

  • You cannot use the stored procedure to grant system database permissions to subaccounts. If you attempt to grant system database permissions to a subaccount, the system displays the following information: 
    Error DatabaseName. Please can not include in ('msdb','master','model','tempdb','rdsadmin') .
  • You cannot use the stored procedure to grant database permissions to system administrators. If you attempt to grant any database permissions to a system administrator, the system displays the following information: 
    Error Login. Please can not include in ('rdsadmin','rdsmirror','rdsbackup','rdsuser') .
  • If an account is already specified in a database, you cannot use the stored procedure to grant the database permissions to the account. Otherwise, the system displays the following information: 
    The proposed new database owner is already a user or aliased in the database.

    In this case, you can delete the subaccount from the database as the rdsuser user first and then execute the stored procedure to grant permissions.

  • If an account has the Create Any Database permission, the stored procedure does not take effect for this account.

Procedure

Run the following command to grant database permissions to a subaccount:

EXEC rdsadmin.dbo.rds_AUTHORIZATION_DatabaseForLogin '@DBName', '@Login';

  • @DBName: indicates the database for which the permissions are to be granted.
  • @Login: indicates the account for which the permissions are to be granted.

For example, to grant permissions of database testDB_1 to account user_1, run the following command:

EXEC rdsadmin.dbo.rds_AUTHORIZATION_DatabaseForLogin 'testDB_1', 'user_1';

After the permissions are granted, the user_1 user can see and perform operations on the testDB_1 database. For databases whose permissions are not granted, the user_1 user cannot see or perform operations on them.

Parent Topic: Usage of Stored Procedures