SQL Server – How to Grant SELECT for Every Database at Server Level

Securitysql serversql server 2014sql-server-2008-r2

I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically.

Ignoring the obvious issue with that, I need to be able to allow an AD account to read any of the data in any of the databases.

Acknowledging the security issue surrounding all of this, is there a way to grant this just once at the server level, rather than having to grant constantly when a new database pops up?
I would prefer to just keep it at a datareader level.

Specifically:
1. databases are created dynamically without advance knowledge of the DBA 2. Reporting jobs iterate through these databases and fail if they encounter a new one.

I am using SQL Server 2008R2 and SQL Server 2014.

Best Answer

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance.

In addition, if databases happen to get created via restore or anything other than create database statement you can use Policy Based Management rules to easily verify if your AD account is a member of that DB role and if not take an appropriate action.

Related Question