Search code examples
sql-serversql-server-2005database-permissions

SQL Server 2005 - Schema Ownership Stuck

I've created a database role in SS2005 and mistakenly made it the owner of the db_denydatareader schema. SSMS will not allow me to untick this option:

enter image description here

This post suggested that I change the ownership of that schema back to dbo (or whatever the default is for that database). However when I attempt to do that, it appears that it is already owned by dbo:

enter image description here

I've tried refreshing things, disconnecting/reconnecting, etc. Am I missing something? I'd just delete the role and start over but can't because it owns a schema!

Thanks.

Solution

  • Oh okay I figured it out. I was looking at the db_denydatareader database role, not the schema, when trying to restore the default owner. The schemas are found in a folder a little further down in SSMS:

    enter image description here

    Resetting the owner of the actual schema cleared that checkbox on the custom role. I feel a bit silly, but also feel like it would be a simple mistake that others might make as well, as the database roles match the schema names.