2

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
   '
sysadmin1138
  • 131,083
  • 18
  • 173
  • 296
starchx
  • 433
  • 10
  • 23

1 Answers1

6
EXEC sp_MSForEachDB
  'USE ?
   IF DBID() >= 5 EXEC sp_addrolemember ''db_datareader'', ''MyUser'' '
gbn
  • 6,009
  • 1
  • 17
  • 21
  • 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 00:01