Sql-Server

MSSQL db_datareader 到所有數據庫

  • August 27, 2011

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
  '
EXEC sp_MSForEachDB
 'USE ?
  IF DBID() >= 5 EXEC sp_addrolemember ''db_datareader'', ''MyUser'' '

引用自:https://serverfault.com/questions/251661