dbo. in vs generated code for stored procedures.

[Rod Snyder]

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

 

[Mythran]

In Enterprise Manager, add the authenticating user (the windows logon for the
developer) to the db_owner (or other role you create that gives access to create
stored procedures). Thsi should allow them to create the stored procedures from
the .Net IDE.

Mythran

 

[Mary Chipman]

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