SQL Server Error: The database principal owns a schema in the database, and cannot be dropped.

Unable to delete SQL Server user from database, error: The database principal owns a schema in the database, and cannot be dropped.

Workaround:

Example: to delete user “testadmin” from database “TestDB”.

USE TestDB;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('testadmin');

-- outcome e.g. db_denydatareader

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s