Wednesday, March 14, 2012

SQL Server - How to Grant Read Access to ALL databases to a Login?



Results of this script should be run on destination server to grant read access to a login to all databases on a server. System databases are skipped

select 'USE ['+ name +  ']' + char(10) + 'GO' + char(10) +
'CREATE USER [ssrsuser] FOR LOGIN [ssrsuser]' + char(10) +
'GO' + char(10) +
'USE [' + name + ']' + char(10) +
'GO' + char(10) +
'EXEC sp_addrolemember N''db_datareader'', N''ssrsuser''' + char(10) + 'GO'
from sys.databases
where database_id > 4
order by name

No comments:

Post a Comment