IS_ROLEMEMBER erroneously returns 0 for database role members?

Question

Running a local instance of SQL Server 2012.

I have created a custom role:

CREATE ROLE [my_user] AUTHORIZATION [dbo]

For all my users (local Windows users and SQL users), I have specified this role for my database (under the User Mappings setting). Thus, the following query should return 1:

SELECT IS_ROLEMEMBER('my_user')

For my Windows-authenticated users it does indeed return 1, but as soon as I'm logged on as an SQL user, it returns 0. I have triple-checked that the SQL user does indeed have this role. What am I missing here?

Update

Performed some more testing. This certainly is weird behavior. I performed these steps:

1. On my local SQL Server I created a database test with user sa. Role my_user added.
2. Logged on as sa in the Management Studio and added MYDOMAIN\MyUser to this role.
3. Re-logged on with Windows Authentication and executed IS_ROLEMEMBER('my_user'). Returns 0.
4. Tried the query using both sa (specifying the username) and the Windows user. Same problem.
5. Tried restarting the SQL Server, just in case.

This makes no sense! If I right-click the role I can see that my Windows user is indeed a member of it. The IS_ROLEMEMBER function is flawed! When I run the following query, it shows that my user is indeed a member of the database role:

SELECT
    USER_NAME(memberuid), USER_NAME(groupuid)
FROM
    sys.sysmembers
WHERE
    USER_NAME(groupuid) = 'my_user'

This also shows my membership:

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

Some additional information:

• I'm on a domain, but currently disconnected. I have seen this problem when connected too though.
• Running Windows 8.1 64-bit.

Update 2

If I explicitly specify the principal as some have suggested, I get this error (executing as sa):

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.

Could it be that IS_ROLEMEMBER experiences the same problem, but does not print the error?

Answer 1

I just had this same issue... I found that the user in question had server roles also assigned to them. When I removed all server roles except 'public', suddenly the is_rolemember query started correctly reporting a 1 instead of the zero..... I tested this back and forth a few times to confirm.

Answer 2

Try specifying the principal explicitly.

SELECT IS_ROLEMEMBER('my_user', 'SqlLogin')

I tested this and it returned 1.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
GO

CREATE ROLE TestRole AUTHORIZATION dbo;
GO

CREATE USER TestUser WITHOUT LOGIN;
GO

EXEC sp_addrolemember 'TestRole', 'TestUser';
GO

SELECT IS_ROLEMEMBER('TestRole', 'TestUser');
GO

Answer 3

Also had this issue. Turns out I had to remove sysadmin server role, then it worked.

This is taken from: https://docs.microsoft.com/en-us/sql/t-sql/functions/is-member-transact-sql

Members of the sysadmin fixed server role enter every database as the dbo user. Checking permission for member of the sysadmin fixed server role, checks permissions for dbo, not the original login. Since dbo can't be added to a database role and doesn’t exist in Windows groups, dbo will always return 0 (or NULL if the role doesn't exist).