Not sure if I just work for the most screwed up company in the world, or if I've just had a long run where I only seem to work for screwed up companies.  Maybe i'm drawn to them in some sort technology sociopath mental illness issue.  I was once told that, ""People with Mental Illness Enrich Our Lives".   If the same applies to a particular companies technology, than i'm enriched a lot!  To the problem...

The infinite wisdom of the powers that be of my company changed the domain\user login from domain\{first initials}{lastname} to a "generic number".  I know there are a lot of "smiths" and "guptas", so the original naming convention was not perfect either, but it worked for 80% of the employees.  The generic number is very frustrating for SQL Server DBA's.

The fun of verifying users for SOX Audits, just became a joy on par with cleaning toilets and replacing sewer lines, no longer easy enough to just run a query to get user's and permissions, now you need to translate the cryptic AD Login, so that management can verify each user....hmmm...thankyou sir, may I have another?  Imagine looking at sp_who2, or Activity monitor, or whatever query you use to view who is connected to your sql server and seeing, {domain}\x111111 as the user who is running a table scan against every table in the most important database you have, and the user was even nice enough to put a table lock hint on the query, nice! 

Now go find x111111 so you can ring his bell, or revoke their access and maybe they'll call you.  The x111111 is useless to you, at least the other AD login convention was useful most of the time, now it's completely useless.  Not only does it frustrate me on troubleshooting problems, but also adding new users.   Even though we modified the ticket system to ask for the login, invariably a significant number of tickets don't include the login name, and we have to bounce tickets around until we can find the users new cryptic AD Login.

To resolve this I had to write a utility that queries active directory and looks up the id of x111111 and returns their name.  I've included the two scripts (vbs - so rename them), one does a lookup by name (so horkay returns a553542) or by id (so a553542 returns Robert Horkay).  We also incorporated them into a web page, so that we could quickly and easily run these queries against Active Directory - (don't forget that your web.config will need to impersonate an identity so that you can query active directory). 

Next I'm going to have to write a custom version of the Activity Monitor, that uses the CLR to translate the cryptic Active Directory Login to a usable name, so that we don't have to bounce between management studio and a web page.