xp_logininfo

This system stored procedure returns information on windows accounts or groups in SQL Server. One of the more useful features of this procedure is that it’ll show any group membership that allows access to the account.

To view all windows accounts and groups in the SQL Server instance, simply run the procedure without parameters:

EXEC xp_logininfo

On my laptop, the result looks like this:

xp_logininfo Without Parameters
xp_logininfo Without Parameters

Unfortunately, I’m writing this on my laptop at home so there are no group memberships to show. That is why the “permission path” column is NULL.

The result set description (from TechNet):

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

To view the results for only one user, use the first parameter (@acctname):

EXEC xp_logininfo 'AsusBook\Tomas', 'all'

The last parameter (‘all’) is used to specify whether to report information about all permission paths for the account, or to report information about the members of the Windows group. Without ‘all’ only the first group is returned. Admin privilege are returned first, and within the privilege they are returned in the order of creation. So with ‘all’ you can get several rows for each user, depending on how many groups the user is part of. If you use the parameter ‘members’ instead of ‘all’, the next level members of the group is returned.

Do not use brackets in the account name. If you do, the following error will occur:

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Could not obtain information about Windows NT group/user '[]', error code 0x534.
Msg 15404, Level 16, State 11, Procedure xp_logininfo, Could not obtain information about Windows NT group/user ‘[]’, error code 0x534.

To view the results for the currently logged in user, use this code:

DECLARE @CURRENTUSER SYSNAME
SET @CURRENTUSER = SUSER_SNAME()
EXEC xp_logininfo @CURRENTUSER, 'all';

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 2 Comments

  1. Chris

    No brackets in the query fixed my error. Thanks!

  2. Bob

    With or without bracket gives me this error.

    execute xp_logininfo ‘AP\tiamsic.bj’,’ALL’

    Could not obtain information about Windows NT group/user ‘AP\tiamsic.bj’, error code 0x5.

Leave a Reply