Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Unanswered: Help with Security Model

    Hi all,

    I need some sugestions from all of you about setting up security model in
    our SQL2000 box.

    The server was setup using Mixed mode. However, all the applications
    (web and MS access) access the server using "sa" userid.

    There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

    Application 1: need read/write access to DB1,DB2 and DB3
    Application 2: need read/write access to DB5
    Application 3: need read/write access to DB4 and DB3

    Should I set up three userids and give them the dbo access to those
    database that they need to use?

    Does that make any sense to you?

    Thank you for all your suggestion

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    The 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

    Use three different UID/Pwd for the applications and GRANT permissions that way.

  3. #3
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    In addition, unless they really NEED dbo, just GRANT the minimum they need.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

    -PatP

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa...

  6. #6
    Join Date
    Feb 2004
    Posts
    52
    Thank you for all your suggestions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •