sp_addrolemember and sp_droprolemember - is there a 'sp_isrolemember'?

Question

Setting up SQLServer with XA Drivers contains a step where you must assign users to the role called "SqlJDBCXAUser"; this can be done using "sp_addrolemember" sproc and undone using the "sp_droprolemember".

Is there a way of checking whether a particular user has been assigned that role ?

Or (better): a way of just listing ALL users assigned to use the specific role here ?

Answer 1

You can use IS_MEMBER

See the manual here - http://msdn.microsoft.com/en-us/library/ms186271.aspx

To list members, use sp_helprolemember or see How to list role members in SQL Server 2008 R2