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

I'm trying to grant read-only access (in other words: select queries only) to a user account on my SQL Server 2008 R2 database. Which rights do I have to grant to the user to make this work?

I've tried several kinds of combinations of permissions on the server and the database itself, but in all cases the user could still run update queries or he could not run any queries (not even select) at all. The error message I always got was

The server principal "foo" is not able to access the database "bar" under the current security context.

Thanks for your help,

Adrian

share|improve this question

2 Answers 2

up vote 2 down vote accepted

I usually do something along these lines:

USE [test]
GO
CREATE USER [ReadOnlyUser] FOR LOGIN [LOCALNT\ReadOnlyNTUser]
GO
EXEC sp_addrolemember N'db_datareader', N'ReadOnlyUser'
share|improve this answer

The first step is to create a user with only db_datareader permissions.

But don't stop there. If this is a login for an actual person that can run any SELECT query they want, they can still cause a lot of problems for a database. This common issue here isn't even a malicious user — just someone who is ignorant about your indexing, performance tuning, and efficient query writing. It's very easy to create a denial of service situation on an sql box this way.

I only know how to do this on a per-query basis, but here at least I know a couple options:

  1. Limit records returned via SET ROWCOUNT.
  2. Use SET QUERY_GOVERNOR_COST_LIMIT. (Note that tuning this can be tricky).

To force a user to include these in their queries, in the past I've used a stored procedure that first executed the appropriate SETs before running the supplied query (in the context of the correct read-only user).

share|improve this answer
    
I understand, but what do you propose as a workaround? Can I somehow restrict the user's resource usage? –  Adrian Grigore Jan 10 '11 at 16:46
    
@Adrian Updated my answer. –  Joel Coel Jan 10 '11 at 16:59
    
If you want to use resource governor, just use resource governor. Don't hack the users query to stuff these settings in. –  mrdenny Jan 10 '11 at 19:14

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.