Modify the permissions of an account

This topic describes the permission scope of each account type. This topic also describes how to modify the permissions of a standard account or a privileged account on an ApsaraDB RDS for SQL Server instance. A system admin account has all permissions on all databases. You do not need to modify the permissions of a system admin account.

Account permissions

For security purposes, ApsaraDB RDS limits specific permissions. The limited permissions are encapsulated in stored procedures. You can run the stored procedures to perform the operations on which the permissions are limited. For more information, see Stored procedures.

Note

You can grant accounts the permissions on the databases of your RDS instance in the ApsaraDB RDS console. If you want to grant accounts the permissions on specific database objects, such as tables, you can execute the required SQL statements. For more information, see GRANT object permissions (Transact-SQL).

System admin account

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

Standard account and privileged account

Authorization objects: databases of the account.

Permission type	Role	Permission
Owner	Server-level roles public processadmin setupadmin Database-level roles public db_owner	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
read-only	Server-level roles public processadmin setupadmin Database-level roles public db_datareader	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 and write permissions (DML)	Server-level roles public processadmin setupadmin Database-level roles public db_datareader db_datawriter	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

Global read-only account

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

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left of the page that appears, click Accounts.
Find the account for which you want to modify permissions and click Change Permissions.
In the Edit Account Permissions panel, modify the permissions of the account.
- Change the authorized databases: Select the databases for which you want to grant the permissions to the account and click the or icon to change the authorized databases.
- Modify the account permissions: In the Authorized Databases section, configure the permissions as needed.
  - Read-Only: Assigns the db_datareader database role in SQL Server.
  - Read/Write (DML): Assigns the db_datawriter and db_datareader database roles in SQL Server.
  - Owner: Assigns the db_owner database role in SQL Server.
  For more information about database-level roles, see Database-level roles.
Click OK in the message that appears.

FAQ

I cannot use the account that has the read/write (DML) permissions to create a table in the database. Why?
A: An account with Read/Write (DML) permissions is assigned the standard db_datareader and db_datawriter database roles in SQL Server. These roles do not include the CREATE TABLE permission. For more information, see Account permissions. 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. For more information, see Modify the permissions of an account.
If you only need to grant the CREATE TABLE permission, use Method 1. This method provides a higher level of security.