troubleshooting login failure when AD group seems to be fine I am using the procedure xp_logininfo to find out what groups a user belongs to.

I have created an AD group called SQLDBA, and added myself to it,
then created a login and granted sysadmin to the group.

EXEC xp_logininfo 'mydomain\myname','all';
    GO




But still when I try to login to that SQL Server instance, using windows authentication, it says I don't have a login?

Where to look at in order to troubleshoot this?



the error message:
18456 (login failed) needs the STATE value to know why it failed. Look in the error log. – Sean Gallardy

Are you sure that if you added yourself tot he group that this sql server is connecting to a domain controller that has the updated info? If you have multiple domain servers it may take some time to have that info replicate a across to all servers. – Jeff A

This is the most comprehensive guide I have seen so far:


Troubleshooting Error 18456 by Aaron Bertrand


You need the state first to use that document. – SqlWorldWide

After you created the AD group and added yourself to it, did you logoff/login? AD Group permissions are only enumerated at login (e.g. the Kerberos Access Token). – John Eisbrener



Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.