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, labGroupA and labGroupB. Both groups are mapped to a database through SSMS, so we have two users inside the db called [labGroupA] and [labGroupB].
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 [labGroupA] 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 [labGroupA] but I cannot explain what exactly happens here.

Regards Dirk

asked Jul 31 '12 at 07:29 PM in Default

DirkHondong
1.3k ● 7 ● 14 ● 17

(comments are locked)

2 answers:

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

SirSQL
4.6k ● 1 ● 3

That is really an interesting fact since I now have some kind of loginless user (labGroupA) and a Windows user who still inherits the rights although I did not grant the right to impersonate labGroupA in any way.

Jul 31 '12 at 08:36 PM DirkHondong

Now I had some kind of aha experience. Just selected everything from sys.user_token for "John Doe" and here I've found the SID of my labGroupA. That brings some light into the darkness.

Jul 31 '12 at 09:00 PM DirkHondong

(comments are locked)

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.

answered Oct 15 '12 at 09:46 AM

brownfc
1

Hi, I have to check my script(s) I've used at that time. But I think, I just started with querying sys.user_token and took a look at the whole result (lazy I am...) http://msdn.microsoft.com/en-us/library/ms188421.aspx

Oct 16 '12 at 07:49 AM DirkHondong

(comments are locked)