xp_logininfo (T-SQL)

Reports the account, the type of account, the privilege level of the account, the mapped login name of the account, and the permission path by which an account has access to Microsoft® SQL Server™.

Syntax

xp_logininfo [[@acctname =] 'account_name'] [,[@option =] 'all' | 'members']
[,[@privelege =] variable_name OUTPUT]

Arguments

[@acctname =] 'account_name': Is the name of a Microsoft Windows NT® user or group granted access to SQL Server. account_name is sysname, with a default of NULL. If account_name is not given, all groups and users that have been explicitly granted login permission are reported. The Windows NT user or group must be qualified by the Windows NT domain or computer name to which the account belongs.
'all' | 'members': Specifies whether to report information on all permission paths for the account, or to report information on the members of the Windows NT group. @option is varchar(10), with a default of NULL. Unless all is specified, only the first permission path is displayed.
[@privelege =] variable_name: Is an output parameter that returns the privilege level of the specified Windows NT account. variable_name is varchar(10), with a default of ‘Not wanted’. The privilege level returned is user, admin, or null.
OUTPUT: When specified, places variable_name in the output parameter.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
account name	nchar(128)	Fully qualified Windows NT account name.
type	char(8)	Type of Windows NT account. Valid values are user or group.
privelege	char(9)	Access privilege for SQL Server. Valid values are admin, user, or null.
mapped login name	nchar(128)	For user accounts with user privilege, mapped login name shows the mapped login name that SQL Server tries to use when logging in with this account, using the mapped rules with the domain name added before it.
permission path	nchar(128)	Group membership that allowed the account access.

Remarks

If account_name is specified as the first parameter, xp_logininfo reports the highest privilege level access for that account. If a user has access as a system administrator and as a user, only the system administrator level (highest privilege) entry is reported. If the user is a member of multiple groups that have the same privilege level, only the first group that matches is reported (the order of the groups is the order that the groups were granted access to SQL Server), and a maximum of one result row is returned.

If account_name is a valid Windows NT account but that account does not have permission to access SQL Server, an empty result set is returned. If account_name cannot be identified as a valid Windows NT account, an error message is returned.

If account_name and all are specified, all permission paths for that account are listed. 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 reported before the user privilege rows, and within a privilege level the row order is the order in which the accounts were granted access to SQL Server. account_name applies to both individual users and groups.

If account_name and members is specified, a list of the next-level members of the group is returned. If account_name is a local group, the listing can include local users, domain users, and global groups. If account_name is a global account, the list consists of domain users. If account_name is a user account, an error message is returned.

Permissions

Execute permissions for xp_logininfo default to members of the sysadmin fixed server role but can be granted to other users.

Examples

This example displays information about the BUILTIN\Administrators Windows NT group.

EXEC xp_logininfo 'BUILTIN\Administrators'

sp_denylogin	xp_loginconfig
sp_grantlogin	System Stored Procedures (General Extended Procedures)
sp_revokelogin