如何授予用户对所有数据库的只读访问权限

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.

我希望一组用户对SQL Server上所有数据库的所有表和视图具有只读访问权限（我正在使用SS2008）。我希望这些用户对所有未来的表和视图具有只读访问权限。

How would you set that up?

你怎么设置它？

3 个解决方案

#1

add the user to the db_datareader role

将用户添加到db_datareader角色

example

例

exec sp_addrolemember 'db_datareader',YourLogin

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

有关db_datareader的信息：http：//msdn.microsoft.com/en-us/library/ms188629（SQL.90）.aspx

#2

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.

在显示userPeter的位置，您必须编写用户名。

#3

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.

您应该只能将用户添加到每个数据库中的db_datareader数据库角色。您可以编写一个脚本来遍历数据库并为您完成。

#1