CODEWITHSUNDEEP
sql-serversecuritysql-server-2008-r2
Lloyd Banks
Lloyd Banks

Reputation: 36649

Granting Same Database Role on All Databases SQL Server 2008 R2

Is there a way to grant a user the same role on all databases? Server-level roles only grant server specific options to users. I'd like to grant a user read-only access to all DBs on a server

Upvotes: 4

Views: 2450

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15251

sp_MSforeachdb might do the trick for you:

EXECUTE master.sys.sp_MSforeachdb '
    use [?];
    declare @user sysname = ''MyUserName''
    if db_name() not in (''master'', ''model'', ''msdb'', ''tempdb'')
    begin
        if not exists (
            select null
            from dbo.sysusers
            where name = @user
        )
        begin
            print ''Adding '' + @user + '' to '' + db_name();
            exec sp_grantdbaccess @user, @user;
        end;
    print ''Granting '' + @user + '' read access to '' + db_name();
    exec sp_addrolemember db_datareader, @user;
    end;
'

Upvotes: 1

Related Questions