I recently ran into an issue where a development manager was creating a new role in SQL for his data warehouse initiative and cube building. He granted the role ownership of a newly created schema then changed his mind on what he wanted regarding a few items.

I provided the explanation to him that I had ran into this in the past and provided the following so that he could transfer the owned schema to another user or role then drop his role he created.

If you try to drop the role while the schema is owned by it SQL will produce the error, “The database principal owns the schema in the database, and cannot be dropped”.

In this particular instance we only had one schema this role was tied to but if a user or role was tied to many you can run the below query to determine what all schema’s are tied to the user:

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘testname’)

The next query will transfer the schema’s you want to move over to dbo for example so you can remove the role then re-assign out as needed:

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

As always I recommend testing locally or in a test environment prior to doing any type of prod related activity.

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