Thursday 12 January 2012

The database principal owns a schema in the database, and cannot be dropped

Encounter this while restoring a SQL Server database backup from one server to another, and trying to delete a user comes from the backup.

The workaround is to list all the schema that user owns by following script:
SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('usertodelete');

then remove or transfer those schema owned by that user to another user, such as dbo by this script:
alter authorization on schema::schema_name to dbo;

No comments:

Post a Comment