none
sql server readonly permission RRS feed

  • Question

  • Which permission under sql server database properties->permission in SSMS should i select to make the database readonly for a user? 
    • Edited by IT researcher Monday, April 1, 2013 8:15 AM
    • Moved by Kalman Toth Monday, April 1, 2013 10:20 AM Not database design
    Monday, April 1, 2013 8:15 AM

Answers

  • Hi, take a look at the database-level roles here.  Basically, you will want to add the specific login to the fixed database role of db_datareader.  In SSMS, you can expand the database where you want the user to have readonly access, right click on the user -> Properties -> User Mapping.  Check the box for db_datareader.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Monday, April 1, 2013 9:04 AM
  • EXEC sp_addrolemeber 'db_datareader', 'someuser'

    This gives the permission to the user to see all data in the database. Note that if the user is member of other groups or roles which heftier permission, the user still have those permission. If you want to avoid that, you can add the the user to the role db_denydatawriter, but be cautious with this, as this can confusion further ahead when you want to grant the user permission, and you have forgotten that you have added him to db_denydatawriter. DENY always takes precedence over GRANT.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 1, 2013 10:36 AM

All replies

  • Hi, take a look at the database-level roles here.  Basically, you will want to add the specific login to the fixed database role of db_datareader.  In SSMS, you can expand the database where you want the user to have readonly access, right click on the user -> Properties -> User Mapping.  Check the box for db_datareader.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.


    Monday, April 1, 2013 9:04 AM
  • EXEC sp_addrolemeber 'db_datareader', 'someuser'

    This gives the permission to the user to see all data in the database. Note that if the user is member of other groups or roles which heftier permission, the user still have those permission. If you want to avoid that, you can add the the user to the role db_denydatawriter, but be cautious with this, as this can confusion further ahead when you want to grant the user permission, and you have forgotten that you have added him to db_denydatawriter. DENY always takes precedence over GRANT.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, April 1, 2013 10:36 AM