sp_addrolesmember all databases 1

[mtorpy]

I have a user QATest and I want to build a script to add said users to have db_datareader access. Is there a way to write it to give it access to all db's as a reader without writing it out 15 times?

use test
if exists (select * from sysusers where name = 'QATest' and issqluser = 1)
begin
PRINT 'QATest already exists in test database.'
exec sp_change_users_login 'Auto_Fix', 'QATest'
end
else
begin
exec sp_grantdbaccess 'QATest'
exec sp_addrolemember 'db_datareader', 'QATest'
end
go

 

[mrdenny]

You can use the sp_MSforeachdb command.

exec sp_MSforeachdb 'use ?
if exists (select * from sysusers where name = ''QATest'' and issqluser = 1)
    begin
    PRINT ''QATest already exists in test database.''
    exec sp_change_users_login ''Auto_Fix'', ''QATest''
    end
else
    begin
    exec sp_grantdbaccess ''QATest'' 
    exec sp_addrolemember ''db_datareader'', ''QATest''
    end'

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mtorpy]

That’s great!!! I have played with this all morning and I am unable to find a way to avoid running the script on the Sys databases master, model, msdb...etc. Any ideas?

I was able to isolate the databases I need by running the below script but how do I incorporate this into the above script?

EXEC sp_msforeachdb
@command1 = 'IF patindex(''%?%'',''tempdb, model, master, msdb, '') = 0
Print ''?'''

 

[mrdenny]

Something like this would work better.

exec sp_MSforeachdb 'use ?
if ''?'' NOT IN ('master', 'model', 'msdb', 'tempdb')
BEGIN
if exists (select * from sysusers where name = ''QATest'' and issqluser = 1)
    begin
    PRINT ''QATest already exists in test database.''
    exec sp_change_users_login ''Auto_Fix'', ''QATest''
    end
else
    begin
    exec sp_grantdbaccess ''QATest'' 
    exec sp_addrolemember ''db_datareader'', ''QATest''
    end'
END

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com