Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 
    BEGIN
       CREATE USER user123 FOR LOGIN user123
       EXEC sp_addrolemember db_datareader, user123
    END
   '
share|improve this question

1 Answer 1

up vote 5 down vote accepted
EXEC sp_MSForEachDB
  '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

 
discard

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.