DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?
Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>