SQL - Add mappings for existing login to multiple DBs

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''
BEGIN
EXEC sp_addrolemember ''db_datareader'', ''MYDOMAIN\MyServiceAccount''
EXEC sp_addrolemember ''db_denydatawriter'', ''MYDOMAIN\MyServiceAccount''
END';

Open in new window


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.
LVL 1
ctp_mackdaddiesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
This may require restarting your instance/server.
0
Steve WalesSenior Database AdministratorCommented:
Try this:

EXEC sp_MSforeachdb N'IF ''?'' LIKE ''%XYZ''
BEGIN
USE [?]
EXEC sp_addrolemember ''db_datareader'', ''MYDOMAIN\MyServiceAccount''
EXEC sp_addrolemember ''db_denydatawriter'', ''MYDOMAIN\MyServiceAccount''
END';

Open in new window


What you're doing is not changing your database context before running the commands.  So you try to access the sp_addrolemember in master each time.

Add in select db_name() before your first EXEC each time - you'll see it spit out the name of the database you were in before you executed your little script (which is usually master by default).

Adding USE[?] will change the session to use the correct database before running each command.

(Tested this on my local test server, it does appear to work)
0
ctp_mackdaddiesAuthor Commented:
I added the USE [?], and that seems to have at least partially worked. If I look at the mappings for that account, I can see those roles were added for the DB (checks next to them), but the checkbox in the Map column is not checked. How would I get those mappings enabled?

Thanks again.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Steve WalesSenior Database AdministratorCommented:
I'm only working with SQL 2005 on my test bed so I may not be seeing what you're seeing.

For 2005, I see no Map check box.  However the following logic should work for you.

Pick your first database.  Select the user.  Right click, select Properties.
Check the Map check box (but don't click OK).

Top Left of the user properties box, you should see a button that says "Script".

That will create the script of what would happen if you clicked OK.

Take that script action, stick it into your sp_msforeachdb script and it should apply to all.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctp_mackdaddiesAuthor Commented:
That gave me
USE [MyDBNameXYZ]
GO
CREATE USER [MYDOMAIN\MyServiceAccount] FOR LOGIN [MYDOMAIN\MyServiceAccount]
GO
USE [MyDBNameXYZ]
GO
EXEC sp_addrolemember N'db_datareader', N'MYDOMAIN\MyServiceAccount'
GO
USE [MyDBNameXYZ]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'MYDOMAIN\MyServiceAccount'
GO

Open in new window


So I got it working using this:
EXEC sp_MSforeachdb N'IF ''?'' LIKE ''%XYZ''
BEGIN
USE [?]
CREATE USER [MYDOMAIN\MyServiceAccount] FOR LOGIN [MYDOMAIN\MyServiceAccount]
USE [?]
EXEC sp_addrolemember N'db_datareader', N'MYDOMAIN\MyServiceAccount'
EXEC sp_addrolemember N'db_denydatawriter', N'MYDOMAIN\MyServiceAccount'
END';

Open in new window


And now the Map column is checked for those DBs. I noticed that for most of them, under the Default Schema column it has "dbo," but for a few of them it has "MYDOMAIN\MyServiceAccount." It's only a few and I'll change them to "dbo." Not sure what impact that would have either way.

Thanks!
0
Scott PletcherSenior DBACommented:
Edit: The user must exist in the db for the permissions to work. End-Edit:

EXEC sp_MSforeachdb N'
IF ''?'' LIKE ''%XYZ''
BEGIN
    USE [?]
    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''MYDOMAIN\MyServiceAccount'')
        CREATE USER [MYDOMAIN\MyServiceAccount] FROM LOGIN [MYDOMAIN\MyServiceAccount]
    EXEC sp_addrolemember ''db_datareader'', ''MYDOMAIN\MyServiceAccount''
    EXEC sp_addrolemember ''db_denydatawriter'', ''MYDOMAIN\MyServiceAccount''
END
';
0
ctp_mackdaddiesAuthor Commented:
Thank you for that IF NOT EXISTS line. Without it, I got errors for the DBs I had already changed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.