Re: Developer design Best Practices

From: David G. (david_nospam_at_nospam.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 15:24:31 -0400

Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
>
> There is NO DBO User in our dev database!!
>
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
>
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
>
> Do you guys think I should be concerned??

I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.

By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.

Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.

I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.

If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.

In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).

In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more. 

-- 
David G.

Relevant Pages

  • Re: Will anyone know this?
    ... You can determine if your login is the database owner with sp_helpdb. ... The only required user in a SQL Server database is dbo and this special ...
    (microsoft.public.sqlserver.security)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: Roles & Users
    ... DBO is simply a user name in a database, albeit a special, privileged one. ... It is the user name assumed by the login listed in sysdatabases as the ... database owner, as well as by any logins in the sysadmin role, or any login ...
    (microsoft.public.sqlserver.server)
  • Re: Change UDF Owner
    ... system_function_schema -especially in the master database. ... Most good judgment comes from experience. ... dbo is in the context of a database, and can be the schema owner for the ... Users can be put in the dbo role, ...
    (microsoft.public.sqlserver.security)
  • Re: Change dbo
    ... I have a database where a user is defined as the dbo on a database. ... I tried using SQL Server Manger ... The login for this person maps to dbo. ...
    (microsoft.public.sqlserver.security)