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:
On my laptop, the result looks like this:
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:
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';