SQL Server login with read access on every database

Here’s a quick script to add a new SQL server login with read access to every database on the server:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_adduser [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_datareader'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_denydatawriter'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

For newer versions of SQL Server, use this:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'CREATE USER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_datareader] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_denydatawriter] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

Be sure to also add to the “model” database. That way you will also have read access to newly created databases.

Leave a Reply