I'm trying to figure out some (for me) weird stuff and by now I have no explanation (and couldn't find any), so maybe someone of you has an idea/explanation regarding the following:
Imagine two active directory groups, labGroupA and labGroupB. Both groups are mapped to a database through SSMS, so we have two users inside the db called [labGroupA] and [labGroupB].
The trouble you are running into here is exactly as you've already figured out, it's to do with the orphaned AD group.
Normally if you had dropped the [labGroupA] login then John would no longer have access to the server, but as you give him a foothold with [labGroupB] then he can get through the front door. Once in he accesses the database and SQL looks to see what permissions his token has. Given that db_owner overrides the db_datareader permissions he continues to inherit that access. Your only option at this point is to revoke db_owner to [labGroupA] or drop the user entirely (which you should have done when dropping the login). There are of course use cases for having users without logins, but that doesn't appear to be a case you are trying to work with.
answered Jul 31 '12 at 08:25 PM
did you manage to sort this out? I've come across the same problem on our SQL Server. The AD Group has been deleted from AD but the Group still exists in SQL Logins and Users. This group has db_owner permissions and if I run sp_validatelogins I can see the group on the list. I assume this is where the users are getting the extra permissions from. How did you run the sys.user_token command for a specific user?
answered Oct 15 '12 at 09:46 AM