Re: Not using SA - Then what?

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 05/31/02 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Fri, 31 May 2002 14:08:39 -0600

 As I said posted earlier, sysadmins and the database owner
(not to be confused with members of db_owners) would have
their objects owned by dbo by default.
Assigning the users to db_owners doesn't mean the objects
they create will automatically be owned by dbo. They would
have to explicitly create them as being owned by dbo, e.g.
create procedure dbo.whatever. Same if they are members of
db_ddladmin - they can create objects and they can be owned
by dbo if they are members of this role but they would need
to qualify the objects with dbo.object.
So you don't need have them to use sa, they just have to get
into the habit of qualifying the owner of the object or you
can have someone like a sysadmin run the scripts for them as
Linda suggested which is also a good idea for quality
control.

-Sue

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

>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
>> >>
>> >>
>> >
>>
>