Modify account permissions for SQL Server - ApsaraDB RDS

ApsaraDB RDS for SQL Server lets you control which databases each account can access and what operations the account can perform on those databases. This topic describes the permission scope of each account type and explains how to modify the permissions of a standard account or a privileged account.

The ApsaraDB RDS console grants permissions at the database level. To grant permissions on specific database objects such as tables, execute the required SQL statements. For more information, see GRANT object permissions (Transact-SQL).

Which account types support permission changes

The following table shows which account types you can modify through the console.

Account type	Modify database permissions	Notes
Standard account	Supported	—
Privileged account	Supported	—
System admin account	Not supported	Has all permissions on all databases by default
Global read-only account	Not supported	Has read-only permissions on all user databases by default; permissions automatically apply to new databases

Account permissions

System admin account

Authorization objects: All databases on the instance
Permissions: All permissions on the RDS instance. For more information, see Permissions (Database Engine).

Global read-only account

Authorization objects: All user databases on the instance
Permission type: Read-only. Permissions automatically apply to new databases created on the instance.

Standard account and privileged account

Authorization objects: Databases authorized to the account.

For security purposes, ApsaraDB RDS encapsulates certain permissions in stored procedures. For more information, see Stored procedures.

The following table describes the three permission levels available for standard accounts and privileged accounts.

Permission type	SQL Server roles assigned	What the account can do
Read-Only	Server-level: `public`, `processadmin`, `setupadmin`; Database-level: `public`, `db_datareader`	Read data from all user tables and views
Read/Write (DML)	Server-level: `public`, `processadmin`, `setupadmin`; Database-level: `public`, `db_datareader`, `db_datawriter`	Read data and add, update, or delete data in all user tables
Owner	Server-level: `public`, `processadmin`, `setupadmin`; Database-level: `public`, `db_owner`	Perform all configuration and maintenance activities on the database, including creating tables, views, procedures, and schemas

For the full list of server-level and database-level permissions included in each level, expand the sections below.

Read-Only — full permission details

Server-level permissions: CONNECT SQL, ALTER ANY LOGIN, ALTER ANY LINKED SERVER, ALTER ANY CONNECTION, ALTER TRACE, VIEW ANY DATABASE, VIEW SERVER STATE, ALTER SERVER STATE

Database-level permissions: CONNECT, SHOWPLAN, SELECT, KILL DATABASE CONNECTION, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION, VIEW ANY COLUMN MASTER KEY DEFINITION, VIEW DATABASE STATE

Read/Write (DML) — full permission details

Server-level permissions: CONNECT SQL, ALTER ANY LOGIN, ALTER ANY LINKED SERVER, ALTER ANY CONNECTION, ALTER TRACE, VIEW ANY DATABASE, VIEW SERVER STATE, ALTER SERVER STATE

Database-level permissions: CONNECT, SHOWPLAN, SELECT, INSERT, UPDATE, DELETE, KILL DATABASE CONNECTION, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION, VIEW ANY COLUMN MASTER KEY DEFINITION, VIEW DATABASE STATE

Owner — full permission details

Server-level permissions: CONNECT SQL, ALTER ANY LOGIN, ALTER ANY LINKED SERVER, ALTER ANY CONNECTION, ALTER TRACE, VIEW ANY DATABASE, VIEW SERVER STATE, ALTER SERVER STATE

Database-level permissions: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, CREATE RULE, CREATE DEFAULT, CREATE TYPE, CREATE ASSEMBLY, CREATE XML SCHEMA COLLECTION, CREATE SCHEMA, CREATE SYNONYM, CREATE AGGREGATE, CREATE ROLE, CREATE MESSAGE TYPE, CREATE SERVICE, CREATE CONTRACT, CREATE REMOTE SERVICE BINDING, CREATE ROUTE, CREATE QUEUE, CREATE SYMMETRIC KEY, CREATE ASYMMETRIC KEY, CREATE FULLTEXT CATALOG, CREATE CERTIFICATE, CREATE DATABASE DDL EVENT NOTIFICATION, CONNECT, CONNECT REPLICATION, CHECKPOINT, SUBSCRIBE QUERY NOTIFICATIONS, AUTHENTICATE, SHOWPLAN, ALTER ANY USER, ALTER ANY ROLE, ALTER ANY APPLICATION ROLE, ALTER ANY COLUMN ENCRYPTION KEY, ALTER ANY COLUMN MASTER KEY, ALTER ANY SCHEMA, ALTER ANY ASSEMBLY, ALTER ANY DATABASE SCOPED CONFIGURATION, ALTER ANY DATASPACE, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT, ALTER ANY MESSAGE TYPE, ALTER ANY CONTRACT, ALTER ANY SERVICE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY FULLTEXT CATALOG, ALTER ANY SYMMETRIC KEY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY SECURITY POLICY, SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT NOTIFICATION, ALTER ANY DATABASE AUDIT, ALTER ANY DATABASE EVENT SESSION, KILL DATABASE CONNECTION, VIEW ANY COLUMN ENCRYPTION KEY DEFINITION, VIEW ANY COLUMN MASTER KEY DEFINITION, VIEW DATABASE STATE, VIEW DEFINITION, TAKE OWNERSHIP, ALTER, ALTER ANY MASK, UNMASK, EXECUTE ANY EXTERNAL SCRIPT, CONTROL

For more information about database-level roles, see Database-level roles.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for SQL Server instance
A standard account or privileged account created on the instance

Modify account permissions

Go to the Instances page. In the top navigation bar, select the region where the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the left navigation pane, click Accounts.
Find the account whose permissions you want to modify and click Change Permissions.
In the Edit Account Permissions panel, configure the permissions:
- Change authorized databases: Select the databases to authorize and click the or icon to move databases between the authorized and unauthorized lists.
- Set permission level: In the Authorized Databases section, select a permission level for each database:
  - Read-Only: Assigns the db_datareader database role
  - Read/Write (DML): Assigns the db_datareader and db_datawriter database roles
  - Owner: Assigns the db_owner database role. Owner grants extensive control over the database, including data definition language (DDL) operations such as creating tables and schemas. Assign this level only when the account genuinely requires those capabilities.
Click OK in the confirmation dialog.

FAQ

Why can't I create a table using an account with Read/Write (DML) permissions?

Read/Write (DML) accounts are assigned the db_datareader and db_datawriter roles, which cover data manipulation (SELECT, INSERT, UPDATE, DELETE) but not data definition. The CREATE TABLE permission is not included.

To create tables in the database, you can use one of the following methods:

Method 1: Execute the required SQL statements. For more information, see GRANT Database Permissions (Transact-SQL) and Database-level roles.
Method 2: Log on to the ApsaraDB RDS console. On the Accounts page of your RDS instance, change the permission type of the account to Owner. The Owner permission type has most permissions, including creating tables. Proceed with caution.

If you only need to grant the CREATE TABLE permission, use Method 1. This method provides a higher level of security.