Why is this user able to work even when the login doesn't exist?

Question

Why is this user able to work even when the login doesn't exist?

Rod at work

SSC-Dedicated

Points: 34008
More actions
May 2, 2017 at 2:13 pm

#325357

I'm trying to figure out how permissions issues with SQL Server. I've added an Active Directory group to a SQL Server user. I'll call the group OURDOMAIN\SomeUsers. So the definition looks like this:
CREATE USER [OURDOMAIN\SomeUsers] FOR LOGIN [OURDOMAIN\SomeUsers]
My problem is, there is no login named OURDOMAIN\SomeUsers. And yet people in that AD group can use apps that hit against the database.
Why?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

is it possible that the SomeUsers group is a member of another group that does have a login on this instance? You can try this to see what the "permission path" is:
xp_logininfo 'OURDOMAIN\SomeUsers' , 'all'

Lowell SSC Guru Points: 323518 More actions · Answer 2

it could also be nested permissions in Active Directory too.

So The users in [OURDOMAIN\SomeUsers] Are in [OURDOMAIN\BusinessAnalysts], and [OURDOMAIN\BusinessAnalysts] was explicitly granted access.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 3

Chris Harshman - Tuesday, May 2, 2017 2:59 PM
is it possible that the SomeUsers group is a member of another group that does have a login on this instance? You can try this to see what the "permission path" is:
xp_logininfo 'OURDOMAIN\SomeUsers' , 'all'

That is really handy Chris, thanks! Investigating what's it gives me.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 4

Lowell - Tuesday, May 2, 2017 3:20 PM
it could also be nested permissions in Active Directory too.
So The users in [OURDOMAIN\SomeUsers] Are in [OURDOMAIN\BusinessAnalysts], and [OURDOMAIN\BusinessAnalysts] was explicitly granted access.

Hmmm. You may have something there. The 2 users who are in the other group that works, have been around forever. Some say they stood in an open field and they built the building we work in around them. Who know how many AD groups they might be in? Guess I'll just have to spend time using AD Users and Computers to try and ferret this out.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 5

Since posting the original message I've noticed something more about the 2 people that are in the AD group that works, as well as the new test account that also works. (This is for one role that works fine with the new application, whereas the other test account and role its in doesn't.) What I've noticed is that those accounts all have logins in Logins at the server level in SQL Server. Whereas only the AD group for the second test user account is there. I really thought that it would work within SQL Server to put an AD group in the Security | Logins, associate that with a user and make sure that user was in the role we want them to be in. Have I got that wrong?

Kindest Regards, Rod Connect with me on LinkedIn.