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?
Best Answer
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.