SQL DBA Tools: 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 to 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 securityadmin fixed server role
• The VIEW SERVER STATE permission

Need help managing SQL Server security? Datazip provides expert DBA services and 24/7 monitoring. Call 1-833-DATAZIP or visit www.SQLBrainBox.com.