SQL Server – See Who Has Access to Databases via AD Group Membership

active-directorypermissionsSecuritysharepointsql server

SharePoint has a button to "Check Permissions". Here you can select a user from Active Directory, and it will magically traverse groups and show you all the access the user has on the site based on what groups they are in.

This is a long shot as I've been working with SQL Server for quite a while and have never heard of this, but just in case I missed something…

Is there anything similar in SQL Server? Right now, checking permissions involves looking at the AD groups that have logins on the instance, and going through AD manually to see who is in which group, and then look at database permissions manually. A "Check Permissions" like SharePoint could save a lot of time.

Best Answer

just seeing which groups the user is in that also have a login on the instance would be nice

You can accomplish this using following code:

exec xp_logininfo 'dom\acc', 'all';

Or you can impersonate that login and use sys.login_token like this:

exec as login = 'dom\acc';
select distinct name
from sys.login_token
where principal_id > 0
      and type = 'WINDOWS GROUP';
revert;
Related Question