SQL DBA Guru

Case Scenario:

I am a DBA and I have got a request to add an user and grant him read access to all databases in a SQL server. Now, the problem is there are more than 50 databases hosted in the server and I do not wan to do it on all databases manually one by one. Is there any way out or a script through which I can grant the read permision to all the databases at one go?

Resolution:

Step 1: Create the Login ID Script first.

Step 2: Change the User ID and Login ID in the following script and execute it which will grant read permission to all the databases.

          EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 
          Begin 
               Print ''?'' 
               Declare @cmd varchar(255) 
               set @cmd = ''USE [?] CREATE USER [User ID] FOR LOGIN [Domain\Windows Login ID] ''

               set @cmd = @cmd + ''EXEC sp_addrolemember N''''db_datareader'''', N''''User ID'''' ''
               exec (@cmd)
               print @cmd
          End'

Note: The above script can be used to grant any sort of permission to all the databases.