Hello Stack Exchange!
I have an interesting problem! Let me set the stage for you:
1) Windows Authentication
2) Default Schema identityiq has been set for the user with login XCORP\USERA
3) Default Database identityiq has been set for the user with login XCORP\USERA
4) XCORP\USERA does NOT have sysadmin
5) XCORP\USERA is the owner of the DB
Ok so as I mentioned - it is interesting for the following reasons - I have a DB account (which we will call IdentityIQUSER) which uses SQL Login authentication with the exact same setup that works just fine! (Exact same setup meaning things that I can check and uncheck in nice little boxes) I can make calls with no issues with the SQL account. Only when I use the windows account am I finding problems. After doing some soul searching I ran the following command that led me to the path that something was amiss (possible with the login) Select SCHEMA_NAME(); and interestingly enough what is returned is not identityiq but dbo so I did some more digging and came across a mountain of effective access. I can provide a list if that would be helpful. So here is my question which is three-fold.
Question 1: Is there any effective access that can be granted that would cause DBO to be the default schema (similar to what happens with sysadmin)
Question 2: Can AD groups control what effective access you are getting on a server? If the answer here is yes, does anyone know how I can track down what group in AD is granting these effective access rights?
Question 3: I have never been this deep in the DB - so I may not be looking at the right thing, so if this isn't the right direction to be marching where else should I look?
Also as a side note, I was looking to see if I could override the effective access with explicit access (since I believe Deny takes precedence) and annoyingly when I logged back in all of my access had been restored...I am probably the first person ever to complain about too much access. lol Thank you for taking the time to look.