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?

score 10 · Accepted Answer · answered Nov 09 '11 at 20:09

10

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

answered Nov 09 '11 at 20:09

gbn

69,809
8
163
243

My fault for leaving out a constraint, in general the "future" databases are actually restores. Restored DBs wouldn't inherit that form master correct? – Kyle Brandt Nov 09 '11 at 20:10
3

@Kyle Brandt: correct, they won't. Unless your restores comes from a box that has this already in the same domain – gbn Nov 09 '11 at 20:11
5

Maybe adding something to the restore script will be the most sensible thing for this scenario... – Kyle Brandt Nov 09 '11 at 20:12

score 3 · Answer 2 · answered Nov 10 '11 at 02:55

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.

score 0 · Answer 3 · answered Apr 28 '16 at 15:14

Another option is to create a SQL Server agent job that runs on a schedule, to execute your SQL for all databases.

You can also wrap your code in a stored procedure, which you can create in the model database or a "Utilities" database.

One possible implementation is below (this assumes the login name and user name will be the same, but can be modified to handle differing login/user names):

CREATE PROCEDURE [dbo].[usp_CreateLoginAndUserDbDataReader]
    @LoginName NVARCHAR(50),
    @Password  NVARCHAR(50),
    @DbName NVARCHAR(128)

AS
BEGIN
    DECLARE @SQL nvarchar(1000)

    IF NULLIF(@LoginName, '') IS NULL
        OR NULLIF(@Password, '') IS NULL
        OR NULLIF(@DbName, '') IS NULL
        THROW 70000, 'You must specify a value for @LoginName, @Password, and @DbName', 1

    IF NOT EXISTS (SELECT name
        FROM master.sys.server_principals
        WHERE name = @LoginName)
    BEGIN
        PRINT '@loginname = ' + COALESCE( @LoginName, 'NULL' )
        SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = ''' + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
        PRINT @SQL
        EXECUTE (@SQL);
    END

    SET @SQL = N'USE [' + @DbName + ']; 
    IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = ''' + @LoginName + ''')
    BEGIN
        CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + ']
    END';
    PRINT @SQL
    EXECUTE (@SQL);

    SET @SQL = 'USE [' + @DbName + ']; ALTER ROLE [db_datareader] ADD MEMBER [' + @LoginName + ']';
    PRINT @SQL
    EXECUTE (@SQL);

    RETURN 0
END

From a SQL Server Agent job scheduled to run daily, or on a desired schedule, you can then call the above like this:

sp_msforeachdb '
USE *DatabaseWhereStoredProcedureResides*
EXEC [usp_CreateLoginAndUserDbDataReader] ''*LoginName*'', ''*UserName*'', ''?''
'

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

3 Answers3

Linked