4

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."

Improve this question
9
  • 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
    Commented Feb 28, 2013 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
    Commented Feb 28, 2013 at 0:43
  • Should xp_logininfo show the members listed within sub-groups? EXEC xp_logininfo 'DOMAIN\GROUP_A','members'
    – diegohb
    Commented Feb 28, 2013 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
    Commented Feb 28, 2013 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
    Commented Feb 28, 2013 at 2:04

1 Answer 1

Reset to default
1

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'.

Improve this answer
1
  • Slow AD replication? That's the only thing I can think of.
    – Jon Seigel
    Commented Feb 28, 2013 at 17:23

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