1

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

2

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'
2

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).

  • 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

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.