October 22, 2008 at 10:08 am
Should the db_owner role always be the owner of the db_owner schema? i.e. is there any reason a particular user might need to own that schema instead of the role owning the schema?
I've got a database where the db_owner schema is owned by a user login, and the person who likely changed the ownership isn't available for questioning, so I'm trying to second-guess myself before I correct what looks like a mistake.
October 22, 2008 at 10:42 am
I'd think it would be the same, but I've never messed with it enough to know if it really matters.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 22, 2008 at 11:30 am
Steve Jones - Editor (10/22/2008)
I'd think it would be the same, but I've never messed with it enough to know if it really matters.
Me neither. It just looks weird, especially since all the other schemas named after database roles are owned by those database roles. I can't find any actual objects associated with the db_owner schema (everything is dbo) so I can't imagine what difference it makes. I've been poking around in BOL and the only thing I've found is that it might affect ownership chains.
Anyway, I'm 95% sure that the original intent was to make the user login a member of the db_owner role, not the owner of the db_owner schema. But I've been wrong concerning the interactions of schemas, roles and permissions before, so I'm sort of just looking for a reality check. Thanks for the reply.
-MIJ
October 22, 2008 at 4:31 pm
Have you checked the default schema for the user that owns the 'db_owner' schema?
I've run into the same situation on an inherited server. I've also discovered that the user that now owns the 'db_owner' schema, has a default schema of itself, but no schema with that name actually exists and it owns no objects.
I suspect that either the schema (with the same name as the user) was dropped at some point, or that someone set up the user wrong. (Schemas are proving to be quite confusing). Maybe this user should have has 'dbo' as the default schema.
October 22, 2008 at 5:54 pm
djn (10/22/2008)
Have you checked the default schema for the user that owns the 'db_owner' schema?
Yes, this database user has 'dbo' defined as it's default schema, just as all the other (non-standard) users do. This particular database doesn't use any custom schemas named after users, nor do any of the other users own any other schemas. This just makes the ownership of the db_owner schema stand out even more. Anyway, I went ahead an changed it, and nothing has blown up yet, so I'm probably OK. I agree that schemas etc. can get a bit confusing, which is why I posted, i.e. I just wanted to know if anyone knew of a good/useful reason for such a setup. Thanks for the reply,
-MIJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply