Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple logins to single DB group Expand / Collapse
Author
Message
Posted Friday, October 11, 2013 9:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:34 AM
Points: 439, Visits: 1,015
Hi all,

I'm wondering if it is possible to map multiple Windows groups logins to a single database user (which is in fact a group)?

Scenario:

DomainA\Agents
DomainB\Agents

each have logins. I would like them to both access the database DB_1 via the same user (group), say, App_Agents.

Can this be done, or do I need separate users / login?

Is there another way to accomplish what I'm trying to do?

Thanks!





Post #1504070
Posted Saturday, October 12, 2013 9:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:24 AM
Points: 153, Visits: 981
schleep (10/11/2013)
Hi all,

I'm wondering if it is possible to map multiple Windows groups logins to a single database user (which is in fact a group)?

Scenario:

DomainA\Agents
DomainB\Agents

each have logins. I would like them to both access the database DB_1 via the same user (group), say, App_Agents.




A Windows-Group can be matched to a SQL Server Login which again an be matched to a User (not group) which then can be put into a database role.
That's it. In short:
WinGroup -> Login -> User -> DBRole

So if you want people from WinGroup1 to be the same DBUser as WinGroup2, you have to put all Members into one Windows Group altogether and start from there.
Makes sense?


Andreas

---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Post #1504211
Posted Sunday, October 13, 2013 5:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,847, Visits: 13,393
What's the reason for matching two user to the same group?
If you want to make sure both groups will use the same permissions, add a separate database.
Example:
CREATE ROLE [App_Agents] AUTHORIZATION [dbo];
EXEC sp_addrolemember N'App_Agents', N'DomainA\Agents';
EXEC sp_addrolemember N'App_Agents', N'DomainB\Agents';

And assign the permissions required to the role instead of the user.
Make sure the windows user groups will not have individual permissions.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1504252
Posted Tuesday, October 15, 2013 5:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:34 AM
Points: 439, Visits: 1,015
Thanks for the responses.

While the logins are unavoidable, I was trying to avoid creating 700+ corresponding database users (groups, in this case). Lutz's response shows it's possible, but... from BOL on sp_addrolemember:

"If the new member is a Windows-level principal without a corresponding database user, a database user will be created but may not be fully mapped to the login. Always check that the login exists and has access to the database."

So while possible to avoid creation of DB users, it doesn't sound like a "sure thing".

At any rate, I've automate the administration using Powershell, so it's mostly painless now.

Thanks again,

P



Post #1504680
Posted Tuesday, October 15, 2013 8:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:23 AM
Points: 728, Visits: 790
You can do this a lot cleaner using nested AD groups, use a Local Group for allocating the SQL Level Permissions then make global groups or universal groups members of the local group (the users go in the global groups and use universals if there are multiple domains in your forrest). Only use SQL Authentication if your application can only work this way.
Post #1504787
Posted Tuesday, October 15, 2013 9:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:34 AM
Points: 439, Visits: 1,015
Hi 360,

I'd originally hoped to use nested AD, but my domain admin tells me "that ain't gonna happen" due to trust issues.

At no point is any of this being done w/ SQL Authentication, it's all integrated, just more complex due to the trust issues.

Thanks

P



Post #1504814
Posted Tuesday, October 15, 2013 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,847, Visits: 13,393
I'm not sure if I follow you completely.

By referring to "700+ corresponding database users" do you mean DomainA\Agents, DomainB\Agents and 698 more "users" (= domain groups)?
If so, I'd say there's a serious issue with your AD structure...

For example, if the windows accounts JohnDoeA, JaneDoeA and JackDoeA all belong to the AD group DomainA\Agents and JohnDoeB, JaneDoeB and JackDoeB all belong to the AD group DomainB\Agents, you'd still only need to create two windows logins: DomainA\Agents and DomainB\Agents, add it to the group App_Agents of the related database and you're all set. At least that's the way we handle it at our place.
It's getting more difficult if you'll need to know whether JohnDoeA or JaneDoeA connected to the db, since both will use the DomainA\Agents login.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1504838
Posted Tuesday, October 15, 2013 11:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:34 AM
Points: 439, Visits: 1,015
45 DBs * 16 groups/Domain/DB = 720 logins.
Each login maps to a DB user, which is a member of a db role.
The roles already exist, as do the DB users for the logins from DomainA.
I was hoping to be able to link the logins from DomainB to those existing DB users, which would save me 2 steps: creating DB users for DomainB groups, and linking those new DB users to their roles.

As I said, it's not so bad: I was able to automate it in the end.
And as long as I don't have to manage the groups' members, I'm happy.




Post #1504871
Posted Monday, October 21, 2013 3:11 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:32 AM
Points: 746, Visits: 568
schleep (10/15/2013)
Hi 360,

I'd originally hoped to use nested AD, but my domain admin tells me "that ain't gonna happen" due to trust issues.

At no point is any of this being done w/ SQL Authentication, it's all integrated, just more complex due to the trust issues.

Thanks

P


I use domain groups all the time and they are a godsend in my view.

Can you expand on the trust issues your domain admin is highlighting? If the user domain(s) are not trusted against the domain the SQL Server is in, then Windows security is never going to work. If (s)he is saying "I cannot be bothered to do this for you as it looks complicated", bribe them to do it or beat them with a stick until they comply. Get management involved if they are dragging their heals.

Echoing other posts: grant access to roles and populate them with logins -> users.

BTW. I'm SQL DBA and domain admin ;o)
Post #1506886
Posted Tuesday, October 22, 2013 10:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 19, 2014 6:34 AM
Points: 439, Visits: 1,015
We only have 1-way trust. The SQL Server domain trusts the outside domain.
AD admin says groups from outside domain could not be members of local groups.

We have a working solution now, so it's moot.

Thanks for taking the time to respond.



Post #1507236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse