Applying Database Principal Throughout the Server (for all Databases) for a Particular User

Ever come across a requirement where you are required to give db_datareader access to a specific user across all the databases on a particular SQL Server. The task is simple as long as you don’t have many databases in the same SQL Server. However, if the number of databases is very high, this can be a very time consuming one.

This can be done either using the GUI (SSMS) or using a T-SQL script. We will consider both options.

Using SQL Server Management Studio

In order to illustrate this, we will create a SQL Login ‘db_user_read_only’ with ‘public’ server role and on the user mapping, we will apply the db_datareader principal.

As mentioned, it would be easy to use the GUI when you have less number of databases. But if the SQL Server contains a lot of databases, this will be a very time consuming job. Then it would be very handy to use the latter approach.

Using T-SQL

You can use the following script to apply the db_datareader principal across all the databases on a particular server.

DECLARE 
    @Sql AS NVARCHAR(MAX)
    ,@UserId AS VARCHAR(MAX) = 'YourLoginId'
SET @Sql = CONCAT('
USE [?];
IF EXISTS (SELECT 0 FROM sys.database_principals AS DP WHERE name = ''',@UserId,''')
BEGIN
    EXEC sys.sp_change_users_login ''update_one'',''',@UserId,''',''',@UserId,'''
END
ELSE
    
    CREATE USER [',@UserId,'] FOR LOGIN [',@UserId,']
    ALTER ROLE [db_datareader] ADD MEMBER [',@UserId,']
')
EXEC sys.sp_MSforeachdb 
    @command1 = @Sql
    ,@replacechar = '?'

Please note the following:

• In the above code, I haven’t excluded the system databases.
• If the login exists on the database, it will map the database user using sp_change_users_login.

Hope this might be very useful to you.