Saturday, 28 December 2019

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 


EXEC master.dbo.xp_logininfo
@privilege='' -- this is not needed

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

EXEC master.dbo.xp_logininfo


EXEC master.dbo.xp_logininfo ' SQLArenaLogin, 'all'

If we would require the members of the AD login specified

EXEC master.dbo.xp_logininfo ' SQLArena_ADTEST, 'members'