Re: dbo. in vs generated code for stored procedures.
From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 10/08/04
- Next message: Mike Labosh: "Re: How can I retrieve data from SQLServer with a specify network"
- Previous message: headware: "Re: newlines in ADO.NET"
- In reply to: Rod Snyder: "dbo. in vs generated code for stored procedures."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 08 Oct 2004 14:18:34 -0400
dbo maps to the sysadmin fixed server role, and it is a good idea that
all of your stored procedures be owned by dbo so that you don't have
an issue with broken ownership chains (see the topics "Using Ownership
Chains" and "Database Owner (dbo)" in SQL BOL). However, in order for
stored procedures to be owned by dbo, the developer creating them has
to be a system administrator. So for example if Jill is a member of
sysadmins, a stored procedure she creates will be named dbo.SomeProc.
If she is not a sysadmin, it will be named Jill.SomeProc. Objects
owned by dbo can be called from client code using "SomeProc", but if
Jill owns SomeProc, then you'll get an error unless the client code
calls Jill.SomeProc.
If you are in the situation where you don't want developers to be
sysadmins, a sysadmin can change the ownership of any object by
executing sp_changeobjectowner. A developer who is a db_owner can
still modify the sproc, they just won't own it any more.
--Mary
On Thu, 7 Oct 2004 16:06:47 -0400, "Rod Snyder" <rod@rcsnyder.com>
wrote:
>We have a team development environment and we just moved to vs.net 2003
>Enterprise so we are now able to create stored procs right out of visual
>studio. In the past we had to use enterprise manager. Our normal procedure
>for db access was to set up a sql user based on the application and use that
>to connect to the application to only execute stored procedures. When we
>created the stored procedures in enterprise manager they defaulted to dbo.
>as the owner/creator. This always caused an error when running the
>application until we physically deleted the two references to
>dbo.[storedproc] out of the vb code. Has anyone else experienced this? I
>have searched extensively and can't find anything similar. We thought moving
>to vs.net2003 and doing the stored procedures there might address this
>issue.
>
>However, now I'm not sure about the best way to have the stored procedures
>created in Visual Studio. We don't want to use the application connection
>since we only
>want that one to have execute permissions. If we use Windows authentication,
>other developers don't appear to have access to the stored procedure. Also,
>vs.net 2003 -- instead of dbo.[stored proc] inserts
>WindowsAccountName.[storedproc] so I don't think this will alleviate the
>first problem.
>
>Any suggestions or ways this is handled in other shops.
>
>Rod
>
- Next message: Mike Labosh: "Re: How can I retrieve data from SQLServer with a specify network"
- Previous message: headware: "Re: newlines in ADO.NET"
- In reply to: Rod Snyder: "dbo. in vs generated code for stored procedures."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
