Re: Not using SA - Then what?

From: Chris Beardsley (clb39@nospam-cornell.edu)
Date: 05/31/02 

From: "Chris Beardsley" <clb39@nospam-cornell.edu>
Date: Fri, 31 May 2002 15:41:40 -0400

I think this thread has expanded more than I orginally thought, but the
nature of these groups is to dispense accurate information...
>From BOL [Database Owner (dbo)]: "Any member of the sysadmin fixed server
role who uses a database is mapped to the special user inside each database
called dbo... any object created by any member of the sysadmin fixed server
role belongs to dbo automatically"

This is different than the discussion about db_owner at the db level,
correct?

If I am reading this correctly, I could assign my three Developers to this
group (assuming I trust them this much), and then any object they create
would be owned by dbo - thus escaping using the SA password on their local
machines.

But more to your earlier point: I will look into templates in SQL 2000 (we
use 6.5, 7, and 2K) and begin to learn how these will aid our test to
production moves.

Again, thank to each of you for your efforts to explain this apparently
confusing issue.

Chris

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:k9hffug3j4a6lf9407bbp2dr668ja8fttd@4ax.com...
> If you are using SQL Server 2000, there are some templates
> you can view through Query Analyzer's objects browser -
> click on the templates tab. We've used templates extensively
> on some projects I've been on for comments, copyrights,
> adherence to coding standards, etc. It definitely helped the
> maintainability of the stored procedures.
> One think to keep in mind though is that members of
> db_owners do not have their objects owned by dbo. They still
> have to be qualified as dbo.object_name. That's one of the
> scenario examples used in the books online topic I referred
> you to.
>
> -Sue
>
> On Fri, 31 May 2002 14:37:14 -0400, "Chris Beardsley"
> <clb39@nospam-cornell.edu> wrote:
>
> >> You can add developers to the db_owner role and use Windows
> >> authentication.
> >> Any objects they create will be owned by dbo.
> >This is the morsel of information I was seeking. But it sounds to me
like I
> >have much more to learn about good security practices... Sue's point of
> >using a template for building objects and your (Linda's) point about
having
> >them build the objects then someone with the proper credentials impliment
> >the code through query analyzer sounds like a solid framework for
security
> >and quality assurance. Now - any idea where I can begin reviewing sample
> >templates ; ) ?
> >
> >Thank you both for your input. Sue - and thank you for some of your other
> >responses which I also gained knowledge from.
> >
> >Chris
> >
> >"lindawie" <lindawie@my-deja.com> wrote in message
> >news:uX#FsiLCCHA.2656@tkmsftngp05...
> >> Chris,
> >>
> >> > I have read that people should not be using SA for anything. How then
> >> > do I have multiple developers/managers login to enterprise manager
> >> > etc but have all the objects owned by dbo (we each have diff NT
> >> > logins)? If SA is stored in clear text in some situations, wouldn't
> >> > all uid/pswrd combo's appear in clear text?
> >> >
> >> > We have wrestled with people making and therefore owning objects, and
> >> > causing headaches in the past...
> >>
> >> Change the SA password and don't give it to anyone.
> >>
> >> You can add developers to the db_owner role and use Windows
> >> authentication.
> >> Any objects they create will be owned by dbo.
> >>
> >> A much better approach is to have developers write scripts to create
all
> >> objects and then you execute the scripts either in Query Analyzer or
> >> in batch using osql.
> >>
> >> Linda
> >>
> >>
> >
>

Relevant Pages

  • Re: Not using SA - Then what?
    ... sysadmins and the database owner ... (not to be confused with members of db_owners) ... they create will automatically be owned by dbo. ... >But more to your earlier point: I will look into templates in SQL 2000 (we ...
    (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: db_owner role in SQL 2k
    ... I do the select user_nameand get dbo ... Do you mean EM lists VDW as the owner of the database? ... I presume then that you are logged in as tfs. ... So my guess is that the login tfs is in the sysadmin role on the server, ...
    (microsoft.public.sqlserver.programming)
  • Re: Groups for dbo
    ... Thanks for the instructions on changing the database owner...I suspect ... While dbo is SUPPOSED to be a special user that's not used for routine ... It's probably safer to change the database owner in any event, ... >> I'm still fairly new to SQL Server, and I'm trying to figure something ...
    (microsoft.public.sqlserver.security)
  • 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)