patternModerate
Best Way to Grant Read All Permission in SQL Server 2008 R2?
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):
Perhaps there is a better way?
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
For existing databases, just run what you have above, once
modelUSE 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.