Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: Best way to setup a Read Only Server Role

    What's the best way to setup security for an account that behaves like a SQL Server Role in that it has only Read Only Rights for all databases both existing and new?

    The problem I'm trying to solve is providing Read Only access to all databases on a SQL Instance for reporting purposes. The trouble is the Read Only access has to extend to new databases that are created. A customized server role would be ideal, but that doesn't exist.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is easy to do, but nearly always a bad idea. I've seen it done twice, and in both cases it caused an audit failure which cost the DBA their job within two years.

    1) Backup the model database
    2) Create a SQL Server login.
    3) Grant that login access to every database (including model)
    4) Make the users created in step 3 members of the database's db_datareader role.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    Thanks Pat.

Posting Permissions

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