We help IT Professionals succeed at work.

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.
Comment
Watch Question

Mike EghtebasDatabase and Application Developer

Commented:
This may require restarting your instance/server.
Steve WalesSenior Database Administrator
Commented:
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)

Author

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.
Senior Database Administrator
Commented:
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.

Author

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!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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
';

Author

Commented:
Thank you for that IF NOT EXISTS line. Without it, I got errors for the DBs I had already changed.