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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,365 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CathyJi-MSFT 21,136 Reputation points Microsoft Vendor
    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.