I have a MS SQL Server 2005 server. I have a login that has db_datareader and db_datadenywriter to one of the DBs on that server. I need to add these same mappings to about 40 more DBs, and I don't want to do it manually... especially since this is for our lab and I'll have to do the same in our production environment later.
All of the DBs are named following a pattern, and they all end with the same last three letters.
I'm trying the following:
EXEC sp_MSforeachdb N'IF ''?'' LIKE ''%XYZ''
EXEC sp_addrolemember ''db_datareader'', ''MYDOMAIN\MyServiceAccount''
EXEC sp_addrolemember ''db_denydatawriter'', ''MYDOMAIN\MyServiceAccount''
This runs successfully, but when I check the mappings for that account, no changes appear to have been made. What am I doing wrong? Or is there a better way to accomplish this?
Thanks in advance.