Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Thanks, tim

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 
       CREATE USER user123 FOR LOGIN user123
       EXEC sp_addrolemember db_datareader, user123
share|improve this question
up vote 5 down vote accepted
  'USE ?
   IF DBID() >= 5 EXEC sp_addrolemember ''db_datareader'', ''MyUser'' '
share|improve this answer
Thanks. Worked well. The DBID() should be DB_ID() for me. Also I changed 5 to 7 due I have ReportServer databases installed. Thanks. Can use this to find out a list of database name and ID: EXEC sp_MSForEachDB 'use ? SELECT DB_ID(), DB_NAME()' – starchx Mar 27 '11 at 0:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.