2

I created a user with reading rights on all the database created by an application in order to run reports. The problem is that this application create a new database for every new projects.

Is there a way for this user to automatically get reading rights to every new database created on this server ?

Thanks

Ulys
  • 23
  • 1
  • 3

2 Answers2

4

Assuming the SQL Server instance is only used for this application you could add the user to the model database with the same permissions. Model is used as the... model for all new databases.

Ally Reilly
  • 366
  • 1
  • 7
  • Unfortunately, no, it will be used by other applications. Thanks anyway. – Ulys Aug 10 '10 at 16:26
  • Since the DDL trigger don't work with dynamic sql, I decided to use the model database. No other application that create database will have access to this server in the future. – Ulys Aug 12 '10 at 14:11
  • 1
    Just be sure that's well documented. Last thing you want is someone other than you creating databases there not knowing that the model has changed. – Shane Aug 13 '10 at 13:52
2

Use a DDL trigger on CREATE_DATABASE see http://technet.microsoft.com/en-us/library/ms186406.aspx to log a row in to a table in a database that will remain constant. Then create a SQL Agent job that looks for new rows in this table and executes a GRANT statement to give rights. Once done flag the row as 'actioned'.

With this solution you can still filter the databases you want the action applied against.

Joel Mansford
  • 985
  • 1
  • 5
  • 13
  • I like that ! I'll give it a try tomorrow. Thanks for teaching me something new. The hardest part will be getting the name of the database out of the event data... – Ulys Aug 10 '10 at 16:25
  • Just leaving this here because this is relevant to the question and could help other people : http://stackoverflow.com/questions/686998/get-database-name-from-ddl-trigger – Ulys Aug 11 '10 at 14:20
  • I might be wrong but won't this add the user to all new databases on the instance too, just as adding to Model would? – Ally Reilly Aug 12 '10 at 10:48
  • No, I can filter the name of the new database to check if the program created it. But I was a bit hasty, it doesn't work at all. To use a variable in a GRANT statement you need to use exec(), and you can't use exec() in a trigger... Back to square one, I may go with models. – Ulys Aug 12 '10 at 13:00
  • 1
    Delayed response(!). This is the reason I was suggesting using a SQL Agent job to perform the GRANT – Joel Mansford Dec 23 '18 at 21:59