Grant a MS SQL login Read Only access to ALL databases
Here is a SQL code snippet I used today that loops through all the databases on a server and creates a SQL script that will give a user Read Only access to all databases (including system databases).
Load up SSMS, open a new Query Window, set the Results To Text output option, change simon-test to the username or group you want to use and Execute this:
select 'use ['+name +']'+ char(13)+'Go'+char(13)+'sp_adduser ''simon-test'' ' +char(13)+'Go'+char(13) + 'sp_addrolemember ''db_datareader'',''simon-test'' ' +char(13)+'Go' +char(13) + ' sp_addrolemember ''db_denydatawriter'',''simon-test'' ' +char(13)+'Go'+char(13) from master..sysdatabases
The resulting text should be cut and paste in to Notepad to clean the output, and then pasted in to a new Query window. Running the script will add the user simon-test to all databases and set the db_datareader and db_denydatawrite roles.
credit has to go to this post – link