title: A real mystery with the behavior of SUSER_SID in a simple T-SQL query
body: <p>I type the following T-SQL code into a sql query window:</p>
  
  <pre><code>-- Get the SUSER_SID standalone
  SELECT SUSER_SID('sql_someuser') AS SUSER_SID_OF_SQL_SOMEUSER
  
  -- Get the SUSER_SID as part of a more complex query (should be the same, right?)
  SELECT UserSid, UserId FROM User..UserSidToUserId 
  UNION ALL
  SELECT SUSER_SID('sql_someuser'),12345
  </code></pre>
  
  <p>Here is the result set I get:</p>
  
  <p><img src="https://i.stack.imgur.com/F6BLq.png" alt="enter image description here"></p>
  
  <p>The first two rows of the second result set come straight out of the <code>UserSidToUserId</code> table. But that last row, the one with the <code>UserId</code> of <code>12345</code> that is added by the second query of the <code>UNION ALL</code>, the one whose <code>UserSid</code> is generated by the second call to <code>SUSER_SID</code> with <strong>the exact same user name</strong> is different!</p>
  
  <p><strong>Update:</strong>
  Just so that there is no confusion, here is the definition of the <code>UserSidToUserId</code> table:</p>
  
  <pre><code>CREATE TABLE dbo.UserSidToUserId
  (
    UserSid uniqueidentifier NOT NULL,
    UserId int NOT NULL,
  )
  </code></pre>
  
  <p>How can two calls with the same user name to <code>SUSER_SID</code> produce results that depend on the context in which <code>SUSER_SID</code> is used, given that it is an absolute call that is context independent?</p>