Granting only SELECT permission on all tables on all databases quickly

Different requirements pop up at different time, unexpectedly. What if someone needs to access all your databases and needs to see all records? This someone could be an Auditor, Security Consultant, or Risk Analyzer. How do you manage it? Create a login account and add user accounts with it to all databases, and then add user accounts to data_reader role? Yes, it might be the way we use it, but remember, there is an easy way, shorter path to do this. It was introduced with SQL Server 2014.

This type of requirement can be managed with granting CONNECT ANY DATABASE permission to the login and then set SELECT ALL USER SECURABLES to the login. CONNECT ANY DATABASE permission allows login account to connect with current databases and even databases that will be created later. It does not allow to do anything else, it is purely for connecting.

Here is the way of doing it;

This code creates a login called Jack.

USE master;
GO
CREATE LOGIN Jack WITH PASSWORD = N'Pa$$w0rd';
GO

If Jack connect to SQL Server instance, he can see databases but he cannot connect with them.

If we grant him CONNECT ANY DATABASE permission;

USE master;
GO
GRANT CONNECT ANY DATABASE TO Jack;
GO

Then he can connect.

Even though Jack can connect, he will not be able to see any of user-defined tables. If the requirement is for allowing him to read data;

USE master;
GO
GRANT SELECT ALL USER SECURABLES TO Jack;
GO

Now he can read records.