Object Owners.

[Erick]

It has been suggested by our DBA group that all developers when
working on objects within a database create objects not under the DBO
owner but under individual owners eg

instead of
dbo.sp_getUsername
the stored procedure would be
smithJ.sp_getUserName

I have never seen this used before in sequel development although I
believe it is a method used by other database ingress/oracle ?

I have never seen any one do this and can't see any benefit accept in
making the developers life more complex as the stored procedures,
tables etc would need to be renamed to belong to the dbo group once
they were moved onto our production server. In addtion code calling
them would need to be modified after testing as we prefix our .Net
calling with the dbo prefix

eg command.CommandText = "dbo.[usp_UpdateSavedSearchId]"

Has any one followed this owner convention and are their any pros and
con's with it.

[Mike Epprecht \(SQL MVP\)]

Hi

What happens to the user's account when he/she leaves the company?
You can't remove the user from SQL Server as the user owns objects.

I work for one of the largest financial institutions, and our policy is that
all object have to be owed by dbo, development, production and UAT. It is an
audit point.

Re-naming object is a lot of work as it is not just the object, but the
references to it and your code. One mistake and it blows up in production.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Erick" <jo**********@hotmail.com> wrote in message
news:e1**************************@posting.google.c om...

It has been suggested by our DBA group that all developers when
working on objects within a database create objects not under the DBO
owner but under individual owners eg

instead of
dbo.sp_getUsername
the stored procedure would be
smithJ.sp_getUserName

I have never seen this used before in sequel development although I
believe it is a method used by other database ingress/oracle ?

I have never seen any one do this and can't see any benefit accept in
making the developers life more complex as the stored procedures,
tables etc would need to be renamed to belong to the dbo group once
they were moved onto our production server. In addtion code calling
them would need to be modified after testing as we prefix our .Net
calling with the dbo prefix

eg command.CommandText = "dbo.[usp_UpdateSavedSearchId]"

Has any one followed this owner convention and are their any pros and
con's with it.

[Malcolm]

Hi Erick,

I have used that approach on a previous project and it does indeed have
the many problems that Mike has stated. In fact, I have seen a quite a
number of processes for managing changes to SQL code and all of them
had major downsides that could potentially leave a schema in an unknown
state i.e. something works in development and yet fails on the test
system even after the required changes have been made.

The only change management system I have used that works is the DB
Ghost Process. This uses a software tool called DB Ghost that enables
you to work with drop/create scripts in source control just like other
code. When the developers have made the required changes to the
'create' scripts DB Ghost is used to build a brand new schema (this
verifies that all the code still works) that is then used as the source
for a compare and upgrade of a target database. This leaves the data
intact on the target database and what it means is that the database
matches the code in source control. If you label/baseline the code in
source control before doing a build and upgrade then you end up with a
target database that COMPLETELY matches a defined set of source scripts
no more, no less.

This process scales really well as it doesn't matter how many
developers are updating the code or how many changes they make - you
still just perform a single build and upgrade using DB Ghost. There
are a lot of tools that can compare two databases but that doesn't give
you the full audit trail that you get with having the scripts under
source control.

Using this method each developer would have his/her own local database
thus also ensuring that no one else can accidentally damage part of the
schema whilst they are working on it.

You can find out more by visiting the website at <a
href="www.dbghost.com">www.dbghost.com</a>