June 4, 2005 at 1:22 pm
On our test boxes, developers are dbo's of the databases they are responsible for. When they create a stored proc, their name, not "dbo", appears as the owner of the stored proc. Why does this happen? Security related? Recommend fix?
TIA,
Bill
P.S. I wrote a simple script to tweak it back to "dbo" but I wonder why it happens in the first place.
June 5, 2005 at 4:26 am
Hi Bill,
When you say the developers are DBO's, do you mean that the database owner is actually mapped to a developer login (as in, when right clicking on the properties of the database, the General tab shows the Owner as the developer)? If so, that's why. You can change the "Owner" of the database to 'sa', and then add the developers logins to the db_owner role in the appropriate databases. This way, when they create objects, they are creating them under the db_owner role, and the default schema is dbo.*.
--Josh
June 5, 2005 at 7:55 am
Bill -
When they create their sp's they just need to write
CREATE PROCEDURE [dbo].[NameOfProcedure]
June 5, 2005 at 8:50 am
>>
When they create their sp's they just need to write
CREATE PROCEDURE [dbo].[NameOfProcedure]
>>
I agree but they often forget to do this!
Bill
June 5, 2005 at 9:02 am
Josh,
Sorry I was unclear. What you describe is exactly the case. The database owner is "sa" but each developer belongs to the "db_owner" role for their database. Still I find that when they create a stored proc if they don't qualify the stored proc name by "dbo", the developer name shows up as the owner of the stored proc.
Bill
June 5, 2005 at 11:05 pm
I have the same problem with my developers.
They are creating tables through a third party ETL tool, and so are
not able to code dbo. in front of the table
I look forward to the solution.
PC
June 6, 2005 at 1:21 am
Are your developers members of the sysadmin server role as described in BOL...
"The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.
For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1."
I sometimes have this problem but just use the stored procedure sp_changeobjectowner to change ownership to dbo
David le Quesne
If it ain't broke, don't fix it...
June 6, 2005 at 4:49 am
You could try using psychology :
Create a job to run overnight that looks for anything created as other than dbo and e-mails the creator with a suitable message. They will soon remember to prefix everything with dbo!
June 7, 2005 at 1:29 am
David,
Excellent quote but where did you find it? I couldn't find it under the updated SQL BOL (typed "systadmin" in the Index.)
TIA,
Bill
June 7, 2005 at 1:30 am
David,
Just saw the quote under "dbo" -- not sysadmin. My fault.
Bill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply