Find the permission path of a Windows logon with XP_LOGININFO

03 July,2013 by Jack Vamvas

I’m managing SQL Server security and need to find the Active Directory group used by the Windows logon account.  The logon is a member of a few Active Directory groups associated with the SQL Server , all with different privileges. How can I find the permission path used?

The Extended Stored Procedure  XP_LOGINFO Returns information about Windows users and Windows groups.  Specifying the account name returns the highest privilege level of the user.

An example :

 

EXEC XP_LOGININFO 'MyDomain\Auser'

--returns
account name	type	privilege	mapped login name	permission path
MyDomain\Auser	user	user	MyDomain\Auser	MyDomain\SQL Support (Dev)

 

This XP_LOGINFO accepts the account name as an input parameter. The recordset returns the highest level of privilege.

Note: This method doesn’t return every Active Directory Group.

To return all the potential permission paths the “all”  parameter will return all the permission paths of the Windows user.

 

 

EXEC XP_LOGININFO 'MyDomain\Auser' , ‘all’

 Read More

Database Server Security Audit Process - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

SQL Server – Powershell Active Directory search - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find the permission path of a Windows logon with XP_LOGININFO


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer