SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Read Only access to all databases to a user in a SQL Server Instance

By Gaurav Deep Juneja,

-----------------------------------------------------------------------------------------------------------------------------
--Script to grant a user read-only access to all the databases at on go in a SQL Server instance except the system databases and 
-- the Log shipped databases(secondary :read-only)
--- Created by : Gaurav Deep Singh Juneja
-----------------------------------------------------------------------------------------------------------------------------
--STEP 1 : Create the Login(Windows or SQL) which needs the db_datareader access. ( 
--For ex: -
-----------------------------------------------------------------
--create login [doamin\username] from windows;
--create login [username] with password='######' ,CHECK_EXPIRATION = OFF,  CHECK_POLICY = OFF;  
--Step 2:  Replace the user with the one that requires access in Set @user in parameters below
USE master
GO
DECLARE @DatabaseName NVARCHAR(100)   
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[username]' –-Replace Your User here
PRINT 'The following user has been selected to have read-only access on all user databases except system databases and log shipped databases:  ' +@user
DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')  
and [state_desc]='ONLINE' and  [is_read_only] <> 1 order by name
OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User + 
    'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'', 
    ' + @User + '';
PRINT @SQL
EXEC sp_executesql @SQL
Print ''-- This is to give a line space between two databases execute prints.
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
  
END  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission
----------------------------Script end-------------------------------------------

Total article views: 323 | Views in the last 30 days: 6
 
Related Articles
FORUM

Grant permission to create table in SP

Grant permission to create table in SP

FORUM

grant access to all databases

grant access to all databases

FORUM

Grant Permission to database object

How to grant a permission?

SCRIPT

Grant Mass permissions

Create A role and grant it a permission on a DB Schemas.

BLOG

How to Grant limited permissions to create views in another schema?

How to Grant limited permissions to create views in another schema? In SQL Server 2005 and 2008R2 y...

Tags
administration    
read only    
security    
 
Contribute