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.
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports