Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

This is the AD group structure:

  • Group A
    • User 4
    • Group B
      • User 1
      • User 2
      • User 3

I granted Group A login access to my SQL instance (connect and dataReader/writer to Northwind db).

I'd expect to be able to login to sql with User 1-4 but in fact only User 4 is allowed to login. If i grant Group B access then all users can login as expected.

So I'd venture to answer my own question and say "NO - sql only respects the 1st-level members of an AD sec group" but I wanted confirmation from someone else.

Logs show state 11 login failure which indicates "Valid login but server access failure."

share|improve this question
    
Was Group B made a member of Group A very recently? The users may have to log off and log on again. See my answer here. –  Jon Seigel Feb 28 '13 at 0:16
    
Thanks for responding.. I read the other posting and I've read other ones like it. I've had User1 log off his machine and log back on and still no luck. Group A and group B were both created/updated like 3 days ago and its very likely users haven't logged off since.. I will have User2 again after logging off first to make sure. –  diegohb Feb 28 '13 at 0:43
    
Should xp_logininfo show the members listed within sub-groups? EXEC xp_logininfo 'DOMAIN\GROUP_A','members' –  diegohb Feb 28 '13 at 0:47
    
confirmed that User2 is not able to login either AFTER clean logoff-logon... any thoughts ? error "Error: 18456, Severity: 14, State: 11." –  diegohb Feb 28 '13 at 1:24
    
I've never used xp_logininfo before -- try it and see what happens. Is either Group A or Group B the BUILTIN\Administrators group by any chance? –  Jon Seigel Feb 28 '13 at 2:04
show 4 more comments

closed as too localized by Jon Seigel, Mark Storey-Smith, dezso, Marian, RolandoMySQLDBA Mar 7 '13 at 6:05

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

This answer may not be of any real value since now the question becomes WHY is there a lag...

but the AD changes were done 3 days ago and in my particular case, it took 3 days before SQL Server started recognizing the new members/groups added.

As of last night, when i executed EXEC xp_logininfo 'DOMAIN\User1','all' it returned nothing.. today (4th day since change) it shows the permission path as 'DOMAIN\GroupA'.

share|improve this answer
    
Slow AD replication? That's the only thing I can think of. –  Jon Seigel Feb 28 '13 at 17:23
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.