Tuesday, January 13, 2015

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

Cause:
This error occurs when you try to remove access to a database for a user that owns the schema.
This generally happens if the user was accidentally set as the owner of the schema

Resolution:

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('myuser')

--Will provide the schema owned by the user

ALTER AUTHORIZATION ON SCHEMA::MySchema TO dbo

--Will set the schema ownership back to dbo, and allow you to modify the security on the user normally

No comments:

Post a Comment