We have a MS SQL Server login and created users belonging to that login in several databases. We want to grant read access to all tables in the databases to these users. What query can we run to grant the read access?

  • Would I be right in assuming that the database user is not present in every user database, but only in some?
    – John K. N.
    Mar 14 '19 at 6:38
  • @hot2use we create one user per database we want to give access to that login so yes users are created for the databases we want that login to see. Mar 14 '19 at 8:31

You should probably grant db_datareader access to that user. Members of the db_datareader fixed database role can read all data from all user tables.

USE master

DECLARE @DatabaseName VARCHAR(32)   
SET @User = '[MyUser]' --Your User

SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; EXEC sp_addrolemember N''db_datareader'', ' + @User + ''

    --EXEC sp_executesql @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission 

With this permission in-place, you won't need to grant read access every time you create a new table.

  • Dang, thought I'd be first to answer a question finally. >.<
    – J.D.
    Mar 14 '19 at 4:27
  • It looks as if this script is trying to grant read access to all non-system databases. We want to allow read access to some of the databases. Mar 14 '19 at 8:34
  • Well.. In order to do that you just need to change the where clause from sysdatabases, specifying the names you need. Mar 14 '19 at 12:03
  • @SandahAung did you have any luck with my script? It should only do the users that belong to each of their respective databases and you can add to the filter on the master.sys.databases table in the first SELECT statement for any other databases you want skipped. Let me know if you need any help. :)
    – J.D.
    Mar 14 '19 at 23:09
  • I accomplished my task by modifying your script a little. Thank you very much for your answer. Mar 15 '19 at 9:50

So I tried to do this without needing a temp table, but in the interest of time (it's almost 1AM by me), this is my v1 of essentially doing the same thing @Dan recommended but without needing a cursor.

Also, it'll automatically adjust all user's permissions for you on execution, instead of needing to pass in one user at a time via a parameter.

-- Temp table of all applicable users in their respective databases (to be used in dynamic SQL later on)
    UserName VARCHAR(500),
    DatabaseName VARCHAR(500)   

DECLARE @Sql_GetUsersByDatabase AS VARCHAR(MAX) = ''

-- Sets up dynamic SQL script to get all users from all databases and load into our temp table
SELECT @Sql_GetUsersByDatabase = @Sql_GetUsersByDatabase + 
'INSERT INTO #TEMP_UsersByDatabase 
SELECT [name] AS UserName, '''
+ d.[name] + ''' AS DatabaseName
FROM [' + d.[name] + '].dbo.sysusers
WHERE hasdbaccess = 1 and [name] <> ''dbo''; ' -- hasdbaccess = 1 -- I believe this filters out non-relevant user accounts, but this filter can be removed if problematic
FROM master.sys.databases AS d
WHERE d.[name] NOT IN ('master','model','msdb','tempdb')

--SELECT @Sql_GetUsersByDatabase -- FOR TESTING

EXEC (@Sql_GetUsersByDatabase);

DECLARE @Sql_AlterUserRoles AS VARCHAR(MAX) = ''

-- Sets up dynamic SQL script to alter the roles and provide db_datareader to each user of each database respectively
SELECT @Sql_AlterUserRoles = @Sql_AlterUserRoles + 'USE [' + ubd.DatabaseName + '] GO ALTER ROLE db_datareader ADD MEMBER ' + ubd.UserName + '; '
FROM #TEMP_UsersByDatabase AS ubd

EXEC (@Sql_AlterUserRoles)

--SELECT @Sql_AlterUserRoles -- FOR TESTING

-- Final cleanup
DROP TABLE #TEMP_UsersByDatabase

Note: I assumed you were using at least SQL Server version 2012 (or greater), but if you're on a lower version of SQL server (e.g. 2008 R2) then replace the dynamic SQL @Sql_AlterUserRoles line of code with this:

SELECT @Sql_AlterUserRoles = @Sql_AlterUserRoles + 'USE [' + ubd.DatabaseName + '] GO EXEC sp_addrolemember db_datareader, ' + ubd.UserName + '; '

The difference is you need to call the sp_addrolemember procedure on SQL 2008 R2 instead of using "ALTER ROLE".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.