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
@acctname='SQLArenaLogin',
@option='all'

Or

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'

0 comments: