how to give Log in permissions on more then 60 Database

• dba.sql29

  SSCommitted

  Points: 1837

  More actions

  December 10, 2013 at 6:09 pm

  #287350

  Hi Experts,

  I have a more then 60 database in one of the server and want to give Single User and AD account "Read only" permission on all 60 database. I have added the User and AD account into SQL Security but don't want to click 60 times to give permission. Is there any script which I can use and it will apply permissions to all 60 database? or any other way which you Experts can tell me I will really appreciate your help.

  Thanks

• arnipetursson

  SSCertifiable

  Points: 6557

  More actions

  December 10, 2013 at 6:21 pm

  #1672482

  In SSMS set results to text and run (replace DOMAIN\login_name with appropriate values):

  select 'USE ' + name + '

  GO

  CREATE USER [DOMAIN\login_name] FOR LOGIN [DOMAIN\login_name]

  GO

  EXEC sp_addrolemember N''db_datareader'', N''DOMAIN\login_name''

  GO

  '

  from master.sys.databases

  where database_id > 4