January 26, 2016 at 2:46 pm
Could someone help me understand the following? I guess in my crude conception, a schema is akin to a namespace. So, I was surprised when I received the following error in an attempt to drop a user account:
"The database principal owns a schema in the database, and cannot be dropped."
and found that this user account owned the following schemas (thanks to Pinal Dave for the help on fixing this problem (http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/):
db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
So I'm confused as to how these database roles are considered own-able schemas. Could someone help explain to me what is going on?
I didn't change the authorization to dbo, though, I changed it all back to the default, which is the name of the role that the schema name above reflects. I'm not sure what effect this will have, but, I'm starting in dev/test. For example, I executed the following:
ALTER AUTHORIZATION ON SCHEMA::db_backupoperator TO db_backupoperator;
and not
ALTER AUTHORIZATION ON SCHEMA::db_backupoperator TO dbo;
Thanks,
--=Chuck
January 27, 2016 at 1:12 am
Those schemas are out of the box owned by the principals of the same name, so you are correct that the ALTER AUTHORIZATION you ran sets it back to default.
The naming and ownership of these schemas is a relic from the past. A long time ago, there was no such concept as schema in SQL Server, but we did have the same two-, three- and four-part names as we do now. Except that we didn't have schema.tablename, but owner.tablename. When the schema-concept was introduced, Microsoft automatically created a schema owned by and named after the user for each user. So if user Bob previously owned a table Bob.HisTable, then this table would now still be Bob.HisTable, a table in the schema Bob, and by extension owned by Bob.
For the database roles such schemas are apparently still created. I don't think anybody every uses them (i.e. puts object in those schemas); and in that case the ownership of those schemas becomes totally irrelevant.
If your company has decided that they do want to have objects in one of those schemas, then you will have to test functionality after the ownership change. Changing the ownership of a schema changes ownership of its objects too, and that can sometimes affect security checking. Especially if ownership chaining applied.
January 27, 2016 at 9:23 am
Thanks Hugo. Luckily, there were no objects within those schemas. Just an ex-employee listed as their owner. Unfortunately he's not here to ask why he was setup in that manner.
--=Chuck
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply