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?
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With