Troubleshooting SQL Server Windows Permissions with xp_logininfo

Understanding SQL Server Windows Permissions

When working as a SQL Server DBA, troubleshooting Windows authentication and permission issues is a common task. One of the most useful tools I rely on is the xp_logininfo extended stored procedure.

Instead of manually digging through Active Directory (AD), xp_logininfo allows you to quickly identify what AD groups a user belongs to—and how those memberships translate into SQL Server permissions.

Why use AD Groups?

Managing access through Active Directory groups is significantly more efficient than handling individual user permissions. In most organizations, users frequently join and leave teams. By assigning access to AD groups instead of individuals:

✅ Access management becomes centralized
✅ Onboarding/offboarding is simplified
✅ Risk of permission misconfiguration is reduced

What is xp_logininfo?

xp_logininfo is an extended stored procedure in SQL Server that returns information about Windows users and groups, including their associated SQL Server login privileges.

It’s particularly useful for:

• Auditing Windows logins
• Troubleshooting permission issues
• Understanding group-based access control

Common Use Cases

1. Check Which AD Groups a User Belongs To
 
EXEC xp_logininfo 'DOMAINNAME\USER', 'all';

This command returns all permission paths for a user, including the AD groups that grant access to SQL Server.

2. List Members of an AD Group

EXEC xp_logininfo 'DOMAINNAME\AD GROUP NAME', 'members';

This shows all members within the specified AD group—but only if that group exists as a login in SQL Server.

Syntax Overview

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

Parameters

• @acctname
  Specifies the Windows user or group (e.g., DOMAIN\user).
• @option
  • 'all': Returns all permission paths for a user
  • 'members': Returns only the members of a group
• @privilege (OUTPUT)

Returns the privilege level of the account:

• • admin
  • user
  • NULL

Permissions Required

To execute xp_logininfo, you must have one of the following:

• Membership in the sysadmin fixed server role
• Membership in the db_owner role (in some contexts)

Additional Examples

• Check Group Information

EXEC xp_logininfo 'BUILTIN\Administrators';

• List Members of a Group
  

EXEC master..xp_logininfo 'DOMAIN\Group', 'members';

• Show All Permission Paths for a User

EXEC xp_logininfo 'DOMAIN\User', 'all';

Important Considerations

Before relying on xp_logininfo, keep the following in mind:

🔹 Domain Controller Connectivity Required
SQL Server must be able to contact a domain controller to retrieve AD information.
🔹 Limited to AD Global Groups
It primarily returns information about global groups in Active Directory.
🔹 Login Must Exist in SQL Server
The account or group must already be defined as a login in SQL Server for results to be returned.