Select from all tables on an Instance

We have all inherited less than desirable SQL Server Instances.  DBAs and Developers will both need to go through the countless databases, tables, SPs, etc. to get a handle of the situation.  In such a fragile situation, we need to ensure we keep our data integrity and prevent our developers from accident updates while trying to trouble shoot an issue.   Here is a nice Server Level Permission set to allow Developers to select from any table in any database on the instance.



First we will need to give the developers the ability to see the database within SSMS (Users maybe able to see all databases by default.)

GRANT VIEW ANY DATABASE TO <RoleSelectAll>
GO;

At this point we can see the database but we can not open a list of the tables.  To do so we must grant the ability to connect.

GRANT CONNECT ANY DATABASE TO <RoleSelectAll>
GO;

Finally, now that we can see the list of tables, we need to be given access to select.

GRANT SELECT ALL USER SECURABLES TO <RoleSelectAll>
GO;