65.9K
CodeProject is changing. Read more.
Home

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

starIconstarIconstarIconstarIconstarIcon

5.00/5 (1 vote)

Sep 10, 2018

CPOL

1 min read

viewsIcon

4440

How to give db_datareader access to a specific user across all the databases on a particular SQL Server

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.

image

image

image

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.