• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 112
  • Last Modified:

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.
0
ctp_mackdaddies
Asked:
ctp_mackdaddies
3 Solutions
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now