Share via

Access to Read-only Database to a non-SA user

Rob Chin 1 Reputation point
2020-08-10T17:16:37.037+00:00

Is there a way to allow a user (non-SA) access a read-only DB without setting the DB to read-write? Thanks.

SQL Server | Other

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 22,401 Reputation points Microsoft External Staff
    2020-08-11T05:58:09.83+00:00

    Hi RobChin-2066,

    Users can only read data in the database and cannot modify the data or database objects when the database sate is set to Read-only.

    From your description, I did a test in my environment. We can’t add a user to the db_datareader role for read-only database, because the database is read-only, we failed to update it.
    16944-annotation-2020-08-11-134946.jpg

    You can try to follow the steps to achieve this goal.

    1. Change the database to read and write state(set the database Read-only option to false from SSMS)
    2. Add the user to the db_datareader role for this database
    3. Change the database to Read-only state(set the database Read-only option to true from SSMS)

    Then the user ( not in admin membership) can access the database.

    16945-annotation-2020-08-11-134947.jpg

    If the response helped, do "Accept Answer" and upvote it.
    Best regards,
    Cathy

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.