Delete a database account on SQL Server and resolve the principal owns a schema error - ApsaraDB RDS

Delete standard accounts, privileged accounts, and SA-privileged accounts from an ApsaraDB RDS for SQL Server instance using the ApsaraDB RDS console or the DeleteAccount API operation.

Warning

Deletion is permanent and cannot be undone. Before you delete an account, make sure the account is no longer used by any applications — existing application connections will fail immediately after deletion. Update your application credentials before proceeding.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for SQL Server instance
Access to the ApsaraDB RDS console with sufficient permissions to manage accounts

Account permission rules

Understanding how deletion affects permissions helps you avoid unexpected access issues.

Privileged account rules

Scenario	Behavior
Create a privileged account	Automatically granted the db_owner role for all existing databases. No manual grant needed.
Add a database	The account does not have access to databases created after the account was created. Modify the permission scope in the console to grant db_owner on the new database.
Delete a privileged account	Permanently removes the account and all its database permissions. The account can no longer log on or perform any database operations.
Recreate a privileged account	Regardless of username, the system automatically grants db_owner on all existing databases in the instance. For databases created afterward, modify permissions manually.

Standard account rules

Scenario	Behavior
Create a standard account	Manually specify authorized databases and permissions: Read/Write, Read-Only, or Owner. If no database is selected, the account has no access to any database.
Add a database	No access to newly created databases by default, even if the account was previously granted permissions. Manually add the database to the account's authorization list and set permissions.
Delete a standard account	All database access permissions are permanently removed. The account can no longer log on, and connections from attached applications will fail. Update the application credentials promptly.
Recreate a standard account (same name)	Original permissions are not restored. The new account starts with no permissions. Re-authorize databases and set permissions manually.

Delete an account

Go to the Instances page. In the top navigation bar, select the region where the instance resides. Find the instance and click its ID.
In the left-side navigation pane, click Accounts.
Find the account you want to delete and click Delete in the Actions column.
In the dialog box, click OK.

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

This error means the account owns one or more schemas. SQL Server blocks deletion of schema owners to maintain schema integrity. Transfer schema ownership to another account before deleting this one.

Solutions

Connect to the instance using SQL Server Management Studio (SSMS).
Transfer schema ownership using one of the following methods:
- 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. Then, transfer ownership of each schema to another account:
```
ALTER AUTHORIZATION ON SCHEMA::<target_schema_name> TO <new_owner_username>;
```
Return to the Accounts page and delete the account.

What's next

Delete an account using the DeleteAccount API operation.
Manage server-level principals with LOGIN users and database-level principals with USER users using command-line tools.