October 12, 2008 at 7:29 am
We use AD Accounts and add people to our AD Account Groups and then assign these AD NT to our SQL
i.e AD SalesGroup
Jan
SalesADmin
Joe
Bob
Sometimes we find that users are deleted from AD i.e lets say Bob then because SalesAdmin
is in SQL it takes a while to go back through AD to realize that he has been deleted when he logs
into AD as DOMAIN\Bob
I like to get a drill down report that shows Domain\Bob belongs to SalesADmin, SalesGroup and
even show that SalesAdmin has read only to DatabaseA.
This is only two levels - we may have many levels down in AD Structure so the time spent
drilling through all the AD levels to find out that Bob got deleted becomes a long process.
Anyone got a report started like this.
October 12, 2008 at 9:00 am
I'm curious why you need to do this? If you remove Bob from SalesAdmin in AD, then he's gone from access to SQL? Or are you adding Bob to SQL Server as a login?
If AD, you'd do the report from there. This isn't really the place for that question. If you have this stored in SQL Server, we can help here.
October 12, 2008 at 9:22 am
Bob is a DOMAIN\BOB whos uses one of our applications and all of a sudden he gets denied access...this is because someone is removing him from AD....then the app to SQL does not work.
So it just takes a why to see which group he got removed from.
It make my life easier to see it all in a mapping display rather than drilling down the multiple groups to find the one that comes to SQL
October 12, 2008 at 9:25 am
Yes this is probrably the wrong envirionment but you can access the AD from SQL so just checking if anyone done something already before i write it 🙂
October 13, 2008 at 8:06 am
We have an OU in AD that we manage for our security. It's our OU, and we manage the Groups. This avoids the situation you are running into, especially when someone is nesting groups. Another conflict can be not everyone in an AD group should get access - for example, a licensed application. File System access - Read / Write access, may not equate to Application / Data access.
I'd consider (if possible) coding Try Catches into your application to log the events. This is much more targeted than enumerating through all the groups on both sides (SQL and AD). Or see if you have a Change Management system to track AD changes.
Maybe I'm missing something, but we use about 50 groups, and a user might participate in 5 or 6. But if I used them as a target in AD, it's very likely they belong to over a hundred groups.
Greg E
October 13, 2008 at 8:21 am
You right on it - i believe there should be the Change Management system to track AD changes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply