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.enter image description here

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

enter image description here

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?

enter image description here

the error message:enter image description here


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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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