SQL Server - How to Grant Read Access to ALL databases to a Login?

Question

I need to give a new login read access to all 300 databases on a server. How can I accomplish this without checking 300 checkboxes in the user mapping area?

Solution 1

One way would be to Set "Results to Text" on the query menu in SSMS then execute the below.

It doesn't actually make the change but generates a script for you to review and execute.

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'user_name',
       @login_name = 'login_name'

SELECT '
    USE ' + QUOTENAME(NAME) + ';

    CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];

    EXEC sys.sp_addrolemember
      ''db_datareader'',
      ''' + QUOTENAME(@user_name) + ''';

    EXEC sys.sp_addrolemember
      ''db_denydatawriter'',
      '''
       + QUOTENAME(@user_name) + '''; 

GO
'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

Or you could look at sys.sp_MSforeachdb as here or Aaron Bertrand's improved version here

If you are not seeing all of the characters when you run this, open the Query Options for Text and check the setting for 'Maximum number of characters displayed in each column'. Make sure this is set to a value large enough to display all characters.

Solution 2

Cursor through the databases and GRANT access to each with a little t-sql.

I did not test the code below.

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 


WHILE @@FETCH_STATUS = 0  
BEGIN  

GRANT SELECT ON DATABASE::@name to 'username'; 

FETCH NEXT FROM db_cursor INTO @name  
END 

Solution 3

EXEC sp_MSForEachDB 
'Declare @name varchar(100)
 select @name = ''?''
 PRINT @name
 IF db_id(@name) > 4
 BEGIN
 USE ?
 CREATE USER [user] FOR LOGIN [user];
EXEC sp_addrolemember ''db_datareader'', ''user''
 END'

Solution 4

I had to tweak Martin Smith's answer slightly as:

1. The spaces and linebreaks resulted in not all the text being generated properly
2. The QUOTENAME in the Exec statement put in square brackets which is incorrect.

My version:

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = 'HelpdeskUser',
       @login_name = 'Helpdesk'

SELECT 'USE ' + QUOTENAME(NAME) + ';
        CREATE USER ' + QUOTENAME(@user_name)
       + ' FOR LOGIN ' + QUOTENAME(@login_name)
       + ' WITH DEFAULT_SCHEMA=[dbo];
    EXEC sys.sp_addrolemember ''db_datareader'',''' + @user_name + ''';
    EXEC sys.sp_addrolemember ''db_denydatawriter'', ''' + @user_name + '''; 
GO'
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 

Otherwise works perfectly. Thanks

Solution 5

Declare @Databases Cursor
Declare @DbName as nvarchar(64)
Declare @Sql nvarchar(max)
Declare @BaseAddUserSql nvarchar(max)
Declare @BaseAddRoleSql nvarchar(max)

Set @Databases = Cursor Fast_Forward For
    select [name]
    from master..sysdatabases
    where [name] not in('master','model','msdb','tempdb')

Open @Databases
Fetch Next From @Databases Into @DbName

Set @BaseAddUserSql = 'exec sp_adduser ''LOGINNAME'''
Set @BaseAddRoleSql = 'exec sp_addrolemember ''db_datareader'', ''LOGINNAME'''


While @@Fetch_Status = 0
Begin
    Begin Try
        Set @Sql = 'Use ' + Quotename(@DbName)
        exec (@Sql)

        Set @Sql = Replace(@BaseAddUserSql, 'LOGINNAME', <loginname>)
        exec(@Sql)

        Set @Sql = Replace(@BaseAddRoleSql, 'LOGINNAME', <loginname>)
        exec(@Sql)
    End Try
    Begin Catch
    End Catch

    Fetch Next From @Databases Into @DbName
End

Close @Databases
Deallocate @Databases

View more solutions

Comments

[Dave Mason]

Since this is a really old question that still gets a lot of views, be aware that in SQL 2014+, GRANT CONNECT ANY DATABASE TO <SQL_Login>; along with GRANT SELECT ALL USER SECURABLES TO <SQL_Login>; is going to be much easier than the old way of doing things.

[Greg]

The t-sql to use after that point is my point of confusion. Any pointers on that?

[buckbova]

Why am I hearing about this sp_msforeachdb just now? I guess I just try and figure out everything with the tools I know. Well, I'm adding that to the toolbox. Thanks!

[Greg]

It says it completed successfully but I don't see any changes. The user mappings area hasn't changed. And the login I put for <loginname> doesn't appear as a user in any of the databases. Any ideas?

[Greg]

Thanks! This seemed to work. I did have the copy the results of that query into textpad and replace Go with \nGo\n

[Martin Smith]

@Greg - Glad it worked. Did you run it in Management Studio? If so selecting the option "Results to Text" (CTRL + T) would probably have avoided the need for the intermediate step.

[Thomas]

@Greg - You might want to comment out the Try-Catch blocks to see the error that is being thrown.

[TheLogicMan]

The default SQL text column limit is 256 so you'll want to increase this in Query -> Query Options -> Results -> Text to avoid the generated scripts getting truncated.

[irag10]

The QUOTENAME around the username & loginname doesn't work for me (SQL2019).