1

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??

  • What is your domain functional level? – Nic Nov 4 '16 at 18:52
  • I can think of using powershell Get-ADGroupMember. I have never seen an AD group name with Over30Chars ! – Kin Shah Nov 4 '16 at 18:54
  • BTW, here is a link about the issue, I forgot to add: connect.microsoft.com/SQLServer/feedback/details/622787/… – LearnByReading Nov 4 '16 at 18:54
  • 1
    Using powershell is not an option for most DB programmers including myself. The solution has to be contained within SQL SERVER – LearnByReading Nov 4 '16 at 18:55
  • ADSI linked server? – Nic Nov 4 '16 at 19:33
3

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.