Sometimes in my career as a DBA I feel like a doctor. Actually, this started before my stint as DBA...way back in my help desk days. Someone on the other end of the phone begins to impart their symptoms and like an ER intern, I listen intently to every detail. Often, like most good practioners, I interjected my questions at the appropriate pause, all the while formulating my conclusions that I would unveil when the person had exhausted themselves with putting together sentence after sentence.  A recent case study went something like this (and as I parenthetically do in these little snippets, I will "sign post" my point - I ultimately would resolve how multiple Windows group membership in SQL worked and pretend all along to have known this while diagnosing the disease of the ODBConomia Braingonemisis):

"Several users can not connect to our new budget application. They have the same rights as all of the other users who can connect. We have tried for 3 days to figure this out so while you are in town, we wanted to see if you could help," the conversation began.

I pondered for a moment and finally asked, "So other users can connect?"

"Yes," came the reply.

"And do the users who can not connect get an error message?"

I knew the response would be affirmative, but lacking the crucual details. It always happens that way. "They can not connect" is generally as far as it gets.

"So what did it say?"

"It is an ODBC error. I did not write it down."

"Is there a user we can test this on?"

"Yes, but she is at lunch."

"Ok.." I wanted desperately to dispatch the interrogator to the corner office to sit an wait for said user while I moved on to other interesting issues. But I was caught in the web of mystery. That is how it goes. I can not give up until I know.

"Who is the user?" I asked finally. I will check out the database.

I got the name. I got the database. I got the server. I got it all. I was onsite with a group of people whom I had met only once and I had all of the information I needed to troubleshoot and damn I troubleshooted (just wanted a past tense there) until I had the answer.  I am skipping ahead swiftly because I am bored with the dialogue. In reality I waited for the user to return from lunch and go through the rote task of making an ODBC connection from her computer and recreated the problem myself...on and on..until all at one I was satisfied. Here it is with no more Quotes.

In SQL, this user had her Windows account assigned to two groups...Group 1) Anatomically Correct and Group 2) Bipolar Dissonance. (Surely these are made up...but we can call them Group A and B..the point being alphabetically sequential.)

In Group A she was assigned to a default database to which she had no access. In Group B she had been granted all required access to the budget database to which she was trying to connect.

I determiend that her group membership alphabetically in Group A, having no access, was preventing Group B from allowing access. (and the other users having identical problems - two birds two birds two birds)

Simply changing the default database for Group A to "Master" to which everyone has access (publicly) allowed her to fall through like Marshall, Will and Holly to Group B and finally get past the ODBC error that had been driving everyone mad for 3 days prior to my arrival.

I explained this in detail to everyone, again as if I had known all along that group membership in SQL Server is apparently alphabetical. And I then quietly finished this blog waiting for all of the readers to dispute this with long treatises on how it really works and making me feel worse and worse until finally I break down and write another book on Reporting Services....Sans dialogue.