Give user read permission to all databases in SQL server ( SQL server read only permission)

To give a user "read only" permissions to a database, where the user can view everything and modify nothing, he/she needs two permissions, db_datareader and VIEW DEFINITION.

In the script below replace   myUserName with the username of the user that you want to give permissions to all the databases in SQL server. On running the script below, this user will get permission to view all tables, stored procedures, functions etc. But would be able to modify none.

declare @username nvarchar(max)

DECLARE @queryForServer nvarchar(max)

DECLARE @queryForEachDB nvarchar(max)

/********************************/

/*ONLY VARIABLE TO BE SET*/

/********************************/

set @username='[myUserName]'

/********************************/

set @queryForServer=

'

IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@username+''')

BEGIN

CREATE LOGIN '+@username+' FROM WINDOWS WITH DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english]

END

'

set @queryForEachDB =

 'use ?

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''+@username+''')

BEGIN

DROP USER '+@username+'

END

CREATE USER '+@username+' FOR LOGIN '+@username+'

GRANT VIEW DEFINITION to  '+@username+'

EXEC sp_addrolemember N''db_datareader'', N'''+@username+'''

'

--If not exists add user at Server Level

EXECUTE sp_executesql @queryForServer

--Add user to each database with datareader and view definition permission

EXECUTE sp_msforeachdb @queryForEachDB