SQL Server: How to grant a permissions to all the databases?

by Ashish Upadhyay

As a DBA you always need to manage permissions across multiple databases specially in a dynamic environment where users/applications are keep adding new databases. It is not practical to do these type of activity manually as it will ve very much time consuming and error prone. 

Below script will help you creating a new users and all READ ONLY permission on all the tables of all the databases on server except four system databases(master, model, msdb and tempdb). 

--Use this script to create user on server.

USE [master]

GO

CREATE LOGIN [TestUser] WITH PASSWORD=N'Please Provide', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

--Use this script to add user and grant permission (in the example READ ONLY permission has been granted) on each databases on the server except four system databases.  

EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')

          BEGIN

               DECLARE @SQL nVARCHAR(255)

               SET @SQL = ''USE [?] EXEC sp_adduser  ''''TestUser''''  ''

               SET @SQL = @SQL + ''EXEC sp_addrolemember N''''db_datareader'''', N''''TestUser'''' ''

              EXEC (@SQL)

             

          END'

 GO