<p>Is there a way of comparing two bitmasks in Transact-SQL to see if any of the bits match? I've got a User table with a bitmask for all the roles the user belongs to, and I'd like to select all the users that have <em>any</em> of the roles in the supplied bitmask. So using the data below, a roles bitmask of 6 (designer+programmer) should select Dave, Charlie and Susan, but not Nick.</p>

<pre>User Table
----------
ID  Username  Roles
1   Dave      6
2   Charlie   2
3   Susan     4
4   Nick      1

Roles Table
-----------
ID  Role
1   Admin
2   Programmer
4   Designer</pre>

<p>Any ideas? Thanks.</p>

Use the Transact-SQL
bitwise AND operator
"&" and compare the result to zero.  Even better, instead of coding the roles as bits of an integer column, use boolean columns, one for each role.  Then your query would simply be designer AND programmer friendly.  If you expect the roles to change a lot over the lifetime of your application, then use a many-to-many table to map the association between users and their roles.  both alternatives are more portable than relying on the existence of the bitwise-AND operator.