Granting read-only access to multiple databases in SQL Server 2014

You may have a group of users which require read-only access to multiple or all databases in a SQL Server instance.
Typically this would be performed by adding the users, or preferably an AD security group which contains the users, to the db_datareader fixed database role in each database.
With SQL Server 2014, the Select All User Securables permission can simplify this process.
As shown below, select the properties of the login.
Grant the login the Select All User Securables permission.
Simply tick the Map option next to each database the login requires read-only access to and select OK.
Now when the user connects to SQL Server, they will be able to query user tables and views in the databases they have been granted access to. They will not be able to insert, delete or modify data unless these privileges are specifically granted.
If the user attempts to access a database which they have not been granted access to, they will receive an error e.g.
If the login is also granted the Connect Any Database permission in addition to Select All User Securables, then the login could access and read user tables and views in all databases without requiring the databases to be selected in the User Mapping tab.

Leave a Reply