Why does xp_logininfo truncates AD user account names? How to overcome this?

Question

As per this question, I use the following SP to see the members within an AD Group that's been added as a "login" in my database server.

EXEC master.dbo.xp_logininfo 'MyDomain\ADGROUPNAME' , 'members'

The issue is that, the resultant data gives me only up to 30 characters of the user's account name. This is because in the Active Directory, there are two sets of account names, one that says pre-windows-2000 , and another is for post-windows 2000. The pre one is truncated and that's the one I get.

For example, if the user account in the AD group is: MyDomain\ShortName, I get MyDomain\ShortName. But if the account name is: MyDomain\HasOverThirtyCharacters, then I get MyDomain\HasOverThirtyCharact

How do I overcome this limitation? What can I use instead of xp_logininfo??

Answer 1

the way I get this done is:

            SELECT @@SERVERNAME
            ----myserver

            DECLARE @NTLogin nvarchar(128) ; 
            SET @NTLogin = 'mydomain\myuser'

            DECLARE @UserList TABLE (
                     [Account Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Type] nvarchar(128) collate Latin1_General_CI_AS,
                     [Privilege] nvarchar(128) collate Latin1_General_CI_AS,
                     [Mapped Login Name] nvarchar(128)collate Latin1_General_CI_AS,
                     [Permission Path] nvarchar(128) )


            INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'all' --insert group information

            IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
               INSERT INTO @UserList EXEC master.dbo.xp_logininfo @NTLogin, 'members' --insert member information

            SELECT  [Server Name] = @@SERVERNAME,
                    [Account Name],
                     [Type],
                     [Privilege],
                     [Mapped Login Name],
                     [Permission Path]                  

            FROM @UserList

you can wrap the code inside a stored procedure with @ntLogin as a parameter, that is how I did it here, including the error handling stuff, which is important too.

Another thing is to install the active directory module for windows powershell

also for testing from inside sql server you can run the following script:

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER 
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER

this will show your credentials

and if you execute as the login you are testing you can see how it looks like.

EXECUTE AS LOGIN='yourdomain\the_long_login'

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(), CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER 
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT