Default Schema not being applied even after setting default schema and default DB

Question

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

6) Have attempted to run new database setup scripts (took down and rebuilt database)

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.

Answer 1

Ok after much troubleshooting and digging, we found our answer! We were aware that groups that are coming from AD can have sysadmin set and that will override anything that is natively set. However, what we didn't know is that if an account is created and added through the MSSQL interface that is connected to this group, it will also carry that server role, even if natively it is off. So let me explain more succinctly.

Step 1: Group A is set to carry the sysadmin role and is an AD group and has been added to MSSQL. Group A - Contains XCORP\USER1

Step2: XCORP\USER1 adds XCORP\USER2 to MSSQL.

following this sequence XCORP\USER2 is now permanently part of the sysadmin role...I don't have any idea why this logic is followed, but it is the end result.

So to fix it:

Step 1: Use an account that is NOT part of the group that contains sysadmin role Step 2: Add another domain user Step 3: Celebrate that you have a normal account.