title: IS_ROLEMEMBER erroneously returns 0 for database role members?
body: <p>Running a local instance of <code>SQL Server 2012</code>.</p>
  
  <p>I have created a custom role:</p>
  
  <pre><code>CREATE ROLE [my_user] AUTHORIZATION [dbo]
  </code></pre>
  
  <p>For all my users (local Windows users and SQL users), I have specified this role for my database (under the <code>User Mappings</code> setting). Thus, the following query should return 1:</p>
  
  <pre><code>SELECT IS_ROLEMEMBER('my_user')
  </code></pre>
  
  <p>For my Windows-authenticated users it does indeed return 1, but as soon as I'm logged on as an <strong>SQL user</strong>, it <strong>returns 0</strong>. I have triple-checked that the SQL user does indeed have this role. What am I missing here?</p>
  
  <h2>Update</h2>
  
  <p>Performed some more testing. This certainly is weird behavior. I performed these steps:</p>
  
  <ol>
  <li>On my local <code>SQL Server</code> I created a database <code>test</code> with user <code>sa</code>. Role <code>my_user</code> added.</li>
  <li>Logged on as <code>sa</code> in the <code>Management Studio</code> and added <code>MYDOMAIN\MyUser</code> to this role.</li>
  <li>Re-logged on with Windows Authentication and executed <code>IS_ROLEMEMBER('my_user')</code>. <strong>Returns 0.</strong></li>
  <li>Tried the query using both <code>sa</code> (specifying the username) and the Windows user. Same problem.</li>
  <li>Tried restarting the <code>SQL Server</code>, just in case.</li>
  </ol>
  
  <p><strong>This makes no sense!</strong> If I right-click the role I can see that my Windows user is indeed a member of it. The <code>IS_ROLEMEMBER</code> function is flawed! When I run the following query, it shows that my user is indeed a member of the database role:</p>
  
  <pre><code>SELECT
      USER_NAME(memberuid), USER_NAME(groupuid)
  FROM
      sys.sysmembers
  WHERE
      USER_NAME(groupuid) = 'my_user'
  </code></pre>
  
  <p>This also shows my membership:</p>
  
  <pre><code>select r.name as role_name, m.name as member_name from sys.database_role_members rm 
  inner join sys.database_principals r on rm.role_principal_id = r.principal_id
  inner join sys.database_principals m on rm.member_principal_id = m.principal_id
  </code></pre>
  
  <p>Some additional information:</p>
  
  <ul>
  <li>I'm on a domain, but currently disconnected. I have seen this problem when connected too though.</li>
  <li>Running Windows 8.1 64-bit.</li>
  </ul>
  
  <h2>Update 2</h2>
  
  <p>If I explicitly specify the principal as some have suggested, I get this error (executing as <code>sa</code>):</p>
  
  <pre><code>SELECT IS_ROLEMEMBER('my_user', 'MYDOMAIN\UserX')
  
  Msg 15404, Level 16, State 19, Line 1
  Could not obtain information about Windows NT group/user 'MYDOMAIN\UserX',
  error code 0x54b.
  </code></pre>
  
  <p>Could it be that <code>IS_ROLEMEMBER</code> experiences the same problem, but does not print the error?</p>