4

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.

rottengeek
  • 690
  • 6
  • 17

2 Answers2

6

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.

  • 1
    You should note that this does not apply to existing databases, only newly created databases. For existing databases you'd have to touch each one or use sp_msforeachdb as mentioned here or ideally sp_foreachdb. – Your_comment_is_not_funny Dec 05 '14 at 20:10
  • ...but for clarity, this won't carry on to databases restored or attached (the OP says "pops up" but I hesitate to assume that only means created). – Aaron Bertrand Dec 05 '14 at 20:10
  • the OP isn't sure if "pops up" means created, either. Life is exciting on this particular server. :) This covers my known problem, though. 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. – rottengeek Dec 05 '14 at 20:14
1

In SQL Server 2014 you could use this two new permissions:

CONNECT ANY DATABASE
SELECT ALL USER SECURABLES

Source

For SQL Server 2008R2 you could add your user to model database or write DDL trigger for database creation action to add your user to new databases, when they are created.

Timofey G Morozov
  • 11
  • 1