xp_logininfo

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 about all permission paths for the account, or to report information about 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- ...- Fully qualified Windows NT account name.
type- ...- Type of Windows NT account. Valid values are user or group.
privilege -...- Access privilege for SQL Server. Valid values are admin, user, or null.
mapped login name -...- 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 -...- Group membership that allowed the account access.