Best Way to Grant Read All Permission in SQL Server 2008 R2?

Question

What is the best method to grant read access to all current and future databases to a user (AD Group in this case)?

Currently I have a flaky scheduled job that does this using (going to start debugging the following, but maybe I am doing it wrong anyways):

sp_msforeachdb '
USE [?]
CREATE USER [MY_DOMAIN\SQL-READ-ALL] FOR LOGIN [MY_DOMAIN\SQL-READ-ALL]
USE [?]
EXEC sp_addrolemember N''db_datareader'', N''MY_DOMAIN\SQL-READ-ALL''
'

Perhaps there is a better way?

Answer 1

For future databases, do this because all DBs are created from model

USE model
EXEC sp_addrolemember N'db_datareader', N'MY_DOMAIN\SQL-READ-ALL'

For existing databases, just run what you have above, once

Answer 2

About the restore: you can set up an Event Notification for the AUDIT_BACKUP_RESTORE event. Not for the faint of hearth, since programming asynchronous event notifications is not exactly easy, but it can do the job you're asking. See Implementing Event Notifications.