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

more ▼

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

DirkHondong gravatar image

1.3k 14 17 19

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Jul 31 '12 at 08:25 PM

SirSQL gravatar image

4.8k 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)
10|1200 characters needed characters left


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?

more ▼

answered Oct 15 '12 at 09:46 AM

brownfc gravatar image


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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 31 '12 at 07:29 PM

Seen: 1369 times

Last Updated: Oct 16 '12 at 07:49 AM