I want to be able to give read only access to my SQL Server 2008 database server. In particular two databases one called "live" and one called "stage".

Essentially the guys who receive this username and password should be able to select from any of the tables, but not perform any update, delete or inserts. Of course they shouldn't be able to perform DDL operations. I could probably get away with banning them from running stored procs if I had to.

Any advice?


Under Security > Logins, create a new login, and in the User Mapping section, tick the two databases, and for each select db_datareader role membership.

This will provide read access to all the data in each DB.

If you want to do it in script, the GRANT command is where you need to start.

An alternative approach is provide access to data through Stored Procedures, and rather than giving them Read permissions to the raw data, grant them Execute permissions to your selected SProcs.

Final Embellishment: If setting up these permissions is fiddly, you might want to create your own Database Role, and you can assign these permissions as described above. The for each user (now and in future), you can simply assign them to that role.

| improve this answer | |

Ideally you would create a windows group login for read access to those databases - assign the DATABASE role db_datareader to that group.

| improve this answer | |
  • 2
    As long as he's using Windows Auth and the users are in the domain, of course. – mfinni Feb 24 '10 at 15:53
  • Yup that's why i said ideally, but i completely agree with your sentiment – CPU_BUSY Feb 24 '10 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.