HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
2008allreadsqlpermissiongrantwayserverbest

Problem

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?

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#7709, answer score: 10

Revisions (0)

No revisions yet.