Extended Stored Procedure xp_logininfo

SQL Server provides a system extended stored procedure xp_logininfo, which is very useful to check domain users and groups granted access to the SQL Server instance. In this blog post I'm going to show how we can use this extended stored procedure to get information about the permission paths for users and groups, and to get information about the members of domain group.

If account_name cannot be identified as a valid Windows user or group, an error message is returned.

If account_name is a valid Windows user or group that is not associated with a SQL Server login, an empty result set is returned.

If account_name is specified, xp_logininfo reports the highest privilege level of the specified Windows user or group. If a Windows user has access as both a system administrator and as a domain user, it will be reported as a system administrator. If the user is a member of multiple Windows groups of equal privilege level, only the group that was first granted access to SQL Server is reported.

If account_name and all are specified, all permission paths for the Windows user or group are returned. If account_name is a member of multiple groups, all of which have been granted access to SQL Server, multiple rows are returned. The admin privilege rows are returned before the user privilege rows, and within a privilege level rows are returned in the order in which the corresponding SQL Server logins were created.

If account_name and members are specified, a list of the next-level members of the group is returned.

By using this stored procedure, database administrator can check account information from domain without bothering domain administrator.