Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 9 down vote accepted

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.

share|improve this answer
add comment

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

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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