Delete a standard account, privileged account, or SA-privileged account - ApsaraDB RDS

You can delete standard accounts, privileged accounts, and SA-privileged accounts from an ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console or by calling an API operation.

Account permission rules

Privileged account management rules

Operation scenario	Permission behavior
Create a privileged account	When you create the account, it is automatically granted the db_owner role for all existing databases. You do not need to grant permissions manually.
Add a database	The account does not have access permissions to databases created after the account is created. Log on to the ApsaraDB RDS console and modify the permission scope of the privileged account. After you grant permissions, the account obtains the db_owner permission on the new database.
Delete a privileged account	The delete operation permanently removes the account and its operation permissions on all databases. The account can no longer be used to log on or perform any database operations.
Recreate a privileged account	If you create a privileged account again, regardless of whether you use the original username, the system automatically grants the account the db_owner permission on all existing databases in the current instance. For databases created after this, you still need to manually modify permissions to grant the account the db_owner permission.

Standard account management rules

Operation scenario	Permission behavior
Create a standard account	When you create the account, you must manually specify the authorized databases and their corresponding permissions: Read/Write, Read-Only, or Owner. If you do not select any database, the account is created but has no access permissions to any database. The account has no permissions on any database. You must manually grant permissions.
Add a database	A standard account does not have access permissions to newly created databases by default. Even if the account was previously granted permissions, it is not automatically associated with new databases. You must manually add the new database to the target account's authorization list and set the corresponding permissions.
Delete a standard account	After the account is deleted, all its database access permissions are permanently purged. The account can no longer be used to log on or perform any database operations. Connections from attached applications will fail. You must update the account and password information promptly.
Recreate a standard account with the same name	Even if you recreate an account with the same name, its original permissions are not automatically restored. The new account is in a "blank state" and has no permissions on any database. You must manually re-authorize databases and set permissions to restore access.

Procedure

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 left-side navigation pane, click Accounts.
Find the account that you want to delete, and click Delete in the Actions column.
In the dialog box that appears, click OK.

References

Delete a database account by calling the DeleteAccount API operation.
Use the command line to manage LOGIN users and manage USER users.

FAQ

How do I resolve the error "`The database principal owns a schema in the database.`" when I delete an account from an ApsaraDB RDS for SQL Server instance?

Problem description

When I try to delete an account on the Accounts page of an ApsaraDB RDS for SQL Server instance, the following error message is reported:

The database principal owns a schema in the database.

Cause

When an account is the owner of a schema, an ownership relationship is established between the account and the schema. SQL Server blocks the deletion of such an account to maintain schema integrity. In this scenario, the account cannot be deleted because it is still the owner of one or more schemas in a database.

Solutions

Connect to the SQL Server instance using SQL Server Management Studio (SSMS).
Query the databases in which the target account is a schema owner, and then transfer the schema ownership to another account.
- Method 1: View and change the schema owner in the SSMS graphical user interface (GUI)
- Method 2: View and change the schema owner using SQL statements
  1. Check which databases contain schemas owned by the target account:
```
USE <target_database_name>;
GO
SELECT sis.name AS SchemaName, soo.name AS OwnerName FROM sys.schemas sis JOIN sys.database_principals soo ON sis.principal_id = soo.principal_id where soo.name='username_to_delete'
```
  2. Change the owner of the database schema to another account:
```
ALTER AUTHORIZATION ON SCHEMA::<target_schema_name> TO <new_owner_username>;
```
After you change the owner, return to the Accounts page of the ApsaraDB RDS for SQL Server instance and delete the target account.