Sql-server – How to grant read-only access to the SQL Server 2008 database

permissionssql serversql-server-2008

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

Best Answer

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'
Related Topic