xp_logininfo Extended Stored Procedure

Reports the account, the type of account, the privilege level of the account, the map name of the account, and the permission path by which the account has access to SQL Server.

Syntax

xp_logininfo ['account_name' [, 'all' | 'members']
[, [@privilege] = variable_name OUTPUT]]

where

account_name: Is the name of a Windows NT - based group or user granted SQL Server access. If no account_name is given, all groups and users that have been explicitly granted login permission are reported.
all | members: Specifies whether to report information on all permission paths for the account, or to report information on the members of the group.
@privilege = variable_name: Is an optional output parameter that returns the privilege level of the specified Windows NT account. The privilege level returned is 'user', 'admin', or 'repl'.

Remarks

If an 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 in which the groups were granted access to SQL Server). 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 is specified as the first parameter and all is specified as the second parameter, all permission paths for that account are listed. If a given username is a member of multiple groups, all of which have been granted access to SQL Server, multiple rows are returned. The system administrator 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. This parameter applies to both individual users and groups.

If account_name is specified as the first parameter and members is specified as the second parameter, then 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.

Examples

A. Login Information for a Single Group

This example returns information about only a single group (admin).

EXEC xp_logininfo 'admin'
go

account name    type    privilege    mapped login name    permission path
-------------    ------    ---------    -----------------    ----------------
SQLSRV\admin    user    (null)    (null)    Everyone

(1 row(s) affected)

These are the results columns for xp_logininfo:



Column heading	Description

account name	The fully qualified account name as known to Windows NT.
type	The type of Windows NT account. Valid values are user, local group, global group, and well-known group.
privilege	The access privilege for SQL Server. Valid values are admin, repl, or user.
mapped login name	For user accounts with user privilege, mapped login name shows the mapped login name that SQL Server will try to use when logging in with this account, using the mapped rules with the domain name added before it.
permission path	The group membership that allowed the account access.

B. Windows NT Account Information

This example shows how the optional output parameter that returns the privilege level of the specified Windows NT account might be used:

DECLARE @value varchar (30)
SELECT @value = NULL
EXEC xp_logininfo '\nwind1\plato', 'all', @value OUTPUT
SELECT 'The NT account nwind1\plato has '''  @value  ''' privilege.'

Permission

Execute permission defaults to the system administrator and can be granted to other users.