Drop failed for DatabaseRole : The database principal owns a schema in the schema and cannot be dropped

This error is raised when there is a schema owned by the role you are trying to drop. The most straight forward and quick fix for this error is to revert the schema ownership to the appropriate role in the database, which will make the dropping role not the owner of any schema in the database anymore.

For example, if you have assigned the db_datareader schema to be owned by the database role you created, you’ll notice that you can’t tick off the owner ship from the role. To work around this issue, simply open the db_datareader database role of the database and make it the owner of db_datareader schema (tick the box in “Schema owned by this role” for db_datareader.) After doing so, you’ll see that the schema in question is not owned by your role, and you’ll be able to drop the database role without any problem.

Advertisements

About Saeed Barghi

Big Data/DW/ETL Consultant based in Melbourne, Australia View all posts by Saeed Barghi

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

%d bloggers like this: