Windows auth, AD groups as db users and a permission phenomenon...
Hi all, 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, lab\GroupA and lab\GroupB. Both groups are mapped to a database through SSMS, so we have two users inside the db called [lab\GroupA] and [lab\GroupB]. GroupA is member of db_owner, GroupB just db_datareader So far, so good. There's a user, let's call him John Doe, who is member in both active directory groups. So John can do everything inside the database. A few minutes, hours, days later, only the GroupA gets removed from SQL Server, the corresponding user [lab\GroupA] still exists inside the database. Then John connects to the SQL Server again and does his selects BUT can still perform DDL operations, can do inserts,updates and deletes as well. I know that it has something to to with the "orphaned" user [lab\GroupA] but I cannot explain what exactly happens here. Regards Dirk
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.
Hi, 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? thanks.