I have a little problem. I know what the problem is, but I can't find any information about it.
I have a windows group login to my SQL2K box. Let's call it DeptA. This DeptA is a Windows Group in AD. They have permission to run a few stored procedures in a DB. A few of these stored procedures need to connect to a different server, so a linked server was set up.
I know that I could set up AD to allow trusted connections and delegation, but my security and network guys don't like that idea. Instead, I've set up a SQL account, and I have DeptA mapped to the SQL account on the linked server.
The problem is, it doesn't work. If I use DOMAIN\Bob, or DOMAIN\Dave, then Bob and Dave in DeptA can run the stored procedures. If I create a mapping for DOMAIN\DeptA, everyone who accesses SQL Server via their group membership of DeptA gets an error.
I'm not worried about the error. I know that I get the error because the linked server connection isn't able to see that the user is a part of the windows group. My problem is that I can't find any documentation on this phenomena, and I also can't find a way round it short of creating a single login account for every new user (which is far too much work if I can avoid it).
Anybody seen/fixed this issue? Or even some documentation somewhere on the problem?