locked
Get MicrosoftAccount login from SQL Server RRS feed

  • Question

  • I found some strange information in log for login auditing. I am using Windows authentication and there are 2 different entries for same user. Sometimes there is ComputerName\User and sometimes MicrosoftAccount\user@email.com. This two accounts are connected. How can i know which account will be used and when?

    How can i extract MicrosoftAccount\user@email.com from SQL Server logins? I am using this query but i am only getting ComputerName\User account with it

    SELECT
    CASE
        WHEN
            CHARINDEX('\', @@SERVERNAME) > 0
        THEN
            REPLACE(name, SUBSTRING(@@SERVERNAME, 1, CHARINDEX('\', @@SERVERNAME) - 1), CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)))
        ELSE
            REPLACE(name, @@SERVERNAME, CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)))
        END
    AS name FROM sys.syslogins ORDER BY name

    Wednesday, March 20, 2019 7:54 PM

Answers

  • Hi Rodjaaa,

    That sounds like a known issue on SQL Server 2016 and 2017. When you create a login that maps to the Windows user account which uses a Microsoft account, the SUSER_SNAME function returns different results. Could you run the following statement and see what it returns.
    EXECUTE AS LOGIN = 'yourlogin'
    SELECT SUSER_SNAME();  
    GO  

    Please refer to FIX: SUSER_SNAME function returns different results between SQL Server 2014 and SQL Server 2016 or 2017.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Puzzle_Chen Thursday, March 21, 2019 1:39 AM
    • Marked as answer by Rodjaaa Thursday, March 21, 2019 9:05 AM
    Thursday, March 21, 2019 1:36 AM

All replies

  • Hi Rodjaaa,

    That sounds like a known issue on SQL Server 2016 and 2017. When you create a login that maps to the Windows user account which uses a Microsoft account, the SUSER_SNAME function returns different results. Could you run the following statement and see what it returns.
    EXECUTE AS LOGIN = 'yourlogin'
    SELECT SUSER_SNAME();  
    GO  

    Please refer to FIX: SUSER_SNAME function returns different results between SQL Server 2014 and SQL Server 2016 or 2017.

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Puzzle_Chen Thursday, March 21, 2019 1:39 AM
    • Marked as answer by Rodjaaa Thursday, March 21, 2019 9:05 AM
    Thursday, March 21, 2019 1:36 AM
  • If i use ComputerName\User then it returns ComputerName\User. If i set MicrosoftAccount\email as login then it returns error
    Wednesday, July 3, 2019 9:56 AM