SQL Server Monitor: Troubleshooting Windows Permissions with xp_logininfo

    May 22, 2026Simon Dang
    SQL Server Monitor: Troubleshooting 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 and 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 is 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, or 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.

    Monitor Your SQL Server Permissions with SQL Brainbox

    Tracking Windows authentication issues and permission changes across multiple SQL Server instances is much easier with the right SQL Server monitor. SQL Brainbox is a free SQL Server monitor that gives you real-time visibility into connections, logins, and server health. Download SQL Brainbox to simplify your SQL Server monitoring and permission troubleshooting.

    © 2026 Crystal Enterprise, LLC. All rights reserved.