Using xp_logininfo to Find Active Directory Logins in SQL Server

The xp_logininfo is an extended stored procedure used to find the windows Active Directory users and groups. In the production environment, I have come across colleagues asking for their permissions on select databases. In such instances, the xp_logininfo can come handy to check the logins for respective windows users and groups.

Here is a sample example of parameters and usage of xp_logininfo stored procedure:

The xp_logininfo has three parameters,

@acctname for specifying the windows AD login

@option for specifying to search either ‘members’ of the login or ‘all’ of them

@privilege for getting the output variable from the command we put in

Example:

EXEC master.dbo.xp_logininfo

@acctname='',

@option=''

@privilege='' -- this is not needed

If we would require all the assigned permission path for the AD login specified

EXEC master.dbo.xp_logininfo