September 10, 2010 at 1:20 pm
Hi Folks,
I've got a stored procedure, ProcA. ProcA is part of the dbo schema. i.e. dbo.ProcA. I show that LoginA owns ProcA from the output of the following:
exec sp_help '[dbo].ProcA'
Name Owner Type Created_datetime
ProcA LoginA stored procedure 2010-07-14 10:09:29.087
I'm curious if this is a bug, or how to be able to reproduce such behavior ( i can't for the life of me reproduce this behavior). I thought the schema ALWAYS owns ALL objects in it, am I mistaken on that point?
sp_changeobjectowner changes the schema as well as the owner, which is not what I'm after.
Thanks for any hints, ideas, or explanations!!
September 10, 2010 at 1:28 pm
In 2005/2008, Schema and Object Owner are two separate things that _sometimes_ line up. In SQL 2000 and earlier, they were more linked together, which is probably what you're comparing it to.
Objects can belong to schemas, users/roles can own schemas, and multiple users can have the same default schema.
As far as that SP changing the schema as well as the owner, I'd be willing to bet that was legacy code from SQL 2K that was never updated, or left in place because most people expect/want it to act that way. The newer way to do things is with ALTER SCHEMA, ALTER AUTHORIZATION, etc..moving away from stored procedures. This is similar to how sp_adduser still works, but CREATE LOGIN/CREATE USER is the preferred way to do things.
September 10, 2010 at 1:32 pm
Derrick,
Thank you! The ALTER AUTHORIZATION is what I was missing. Now I am able to have ProcB a member of dbo schema, but owned by LoginA as well.
I really appreciate the help. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply