IS_MEMBER function does not work as expected - by magasvs

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 345809 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 5/23/2008 10:43:47 AM
Access Restriction Public


IS_MEMBER function returns 0 even if a user is member of the user-defined database role.  It has to be 1 according to the Books Online: "Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role. ....
....' role '  - Is the name of the SQL Server role that is being checked. role is sysname and can include the database fixed roles or user-defined roles, but not server roles."

This happens when the user is member of sysadmin server role.

Sign in to post a comment.
Posted by Georg Scholz on 7/27/2015 at 5:52 AM
In my opinion this is still either a bug or at least the function is improperly documented.
It should be clearly documented that IS_MEMBER('customrole') will ALWAYS return 0 when you are sysadmin, regardless if you are member of your custom role or not.

For example, if you have some code like IF IS_MEMBER(xxx) ... then this does not work if you are a sysadmin.
Posted by DeathByVisualStudio on 7/22/2010 at 1:54 PM
This is yet another bad design where Microsoft expects the developer to know of the exceptions even though they are not documented. Having a simple feature like this work "except for" just adds more lines of code to deal with the exception while providing no value in return.
Posted by VG_Developer on 9/3/2009 at 11:06 AM
I agree with tican. If my user is a member of a role, I'm still a member even if I have "super user" permissions as a sysadmin.
Posted by Vidar Nordnes on 2/10/2009 at 6:56 AM
My login has the following permissions:
0 sysadmin
1 db_owner
1 af_developer

I get the same results for IS_MEMBER('af_developer') if I add permissions to sysadmin as if I'm "only" db_owner.
I think you should return 1 in this case regardless of which other roles I'm a member of. If I'm a member of af_developer, IS_MEMBER('af_developer') should return 1 even if I'm sysadmin(!)
Posted by Microsoft on 5/30/2008 at 4:49 PM

Thanks for the clarification as this explains things. The reason that you are seeing this behaviour is because, internally, any member of the sysadmin role is mapped to dbo at the database level. As a result, what you're seeing is expected and by-design.

Posted by magasvs on 5/30/2008 at 3:27 PM
I made corrections to the post. It does not work only if the user is member of the sysadmin server role.
Posted by Microsoft on 5/28/2008 at 4:26 PM

Unfortunately, I have been able to repro your problem. Following your steps, I correctly get a "1" returned. Is it possible that you issued the command against the wrong database?