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.

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.

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.

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.

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.