Grant a MS SQL login Read Only access to ALL databases

 Here is a SQL code snippet I used today that loops through all the databases on a server and creates a SQL script that will give a user Read Only access to all databases (including system databases).

 Load up SSMS, open a new Query Window, set the Results To Text output option, change simon-test to the username or group you want to use and Execute this: 

select 'use ['+name +']'+ char(13)+'Go'+char(13)+'sp_adduser ''simon-test''

' +char(13)+'Go'+char(13) + 'sp_addrolemember ''db_datareader'',''simon-test''

' +char(13)+'Go' +char(13) + ' sp_addrolemember ''db_denydatawriter'',''simon-test''

' +char(13)+'Go'+char(13) from master..sysdatabases


 The resulting text should be cut and paste in to Notepad to clean the output, and then pasted in to a new Query window.  Running the script will add the user simon-test to all databases and set the db_datareader and db_denydatawrite roles.

credit has to go to this post – link

Posted on June 3, 2013 at 20:16 by simon · Permalink

Leave a Reply