I have 100 databases in MSSQL 2008 Server. I need to create a read only user to read all these databases. Is there a server wide role: db_datareader I can assigned the user to, so that I don't need to go through each database to add user mapping.
For whom it may helps, this is my final script:
--You will need Server Level's Login user123 created first. -- >=7 filtered out the system database, use this to find out your number -- EXEC sp_MSForEachDB 'use ? SELECT DB_ID(), DB_NAME()' EXEC sp_MSForEachDB 'USE ? IF DB_ID() >= 7 BEGIN CREATE USER user123 FOR LOGIN user123 EXEC sp_addrolemember db_datareader, user123 END '