How to grant users read-only access to all databases

Question

I want a group of users to have read-only access to all tables and views on all databases on SQL Server (I'm using SS2008). I'd like those users to have read-only access to all future tables and view.

How would you set that up?

Since this is a really old question that still gets a lot of views, be aware that in SQL 2014+, `GRANT CONNECT ANY DATABASE TO ;` along with `GRANT SELECT ALL USER SECURABLES TO ;` is going to be much easier than the old way of doing things. — Dave Mason, Aug 13 '21 at 13:37

score 10 · Accepted Answer · answered Jul 07 '10 at 15:59

10

add the user to the db_datareader role

example

exec sp_addrolemember 'db_datareader',YourLogin

Info about db_datareader: http://msdn.microsoft.com/en-us/library/ms188629(SQL.90).aspx

answered Jul 07 '10 at 15:59

SQLMenace

132,095
25
206
225

6

This gives read-only access to a single database, not to all databases. You will have to do this for each database. – Martin Liversage Jan 27 '16 at 15:09

MyCuky LoVe · Answer 2 · 2016-06-29T12:06:38.957

DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR

LOCAL FAST_FORWARD
FOR SELECT name FROM MASTER.dbo.sysdatabases OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname 
WHILE @@FETCH_STATUS = 0
BEGIN
    /* This sentence will be executed to gran the privileges. */
    SELECT @statement = 'use ['+@dbname+']; '+'EXEC sp_addrolemember N''db_datareader'', N''userPeter''';
    EXEC sp_executesql @statement
    FETCH NEXT FROM db_cursor INTO @dbname
END

In the location that appear userPeter you must write your username.

score 0 · Answer 3 · answered Jul 07 '10 at 16:04

0

You should just be able to add the users to the db_datareader database role in each of the databases. You could write a script to loop through the databases and do it for you.

answered Jul 07 '10 at 16:04

Tom H

46,766
14
87
128

How to grant users read-only access to all databases

3 Answers3

Linked