How can I grant read-only access to my SQL Server 2008 database?

Question

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

Solution 1

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'

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

Comments

[Adrian Grigore]

I understand, but what do you propose as a workaround? Can I somehow restrict the user's resource usage?

[Joel Coel]

@Adrian Updated my answer.

[Ryan Ferretti]

If you want to use resource governor, just use resource governor. Don't hack the users query to stuff these settings in.