October 11, 2013 at 9:36 am
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!
October 12, 2013 at 9:59 am
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
October 13, 2013 at 5:02 am
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.
October 15, 2013 at 5:15 am
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
October 15, 2013 at 8:33 am
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.
October 15, 2013 at 9:16 am
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
October 15, 2013 at 10:00 am
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.
October 15, 2013 at 11:11 am
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.
October 21, 2013 at 3:11 pm
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)
October 22, 2013 at 10:27 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply