Databases
StackOverflow2013
Postsdbo
POIS_ROLEMEMBER erroneously returns 0 for database role members?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVignesh Kumar A
    1. USl33t
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. USl33t
      1. VTBountyStart
    3. VO
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat about `IS_MEMBER()`?
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. USDamien_The_Unbeliever
    2. COTried that. Same result.
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. USl33t
    3. COdoes it really return 0 or NULL?
      singulars
      1. POIS_ROLEMEMBER erroneously returns 0 for database role members?
      1. USPrfctByDsgn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload