Find out if current user is member of a role

I need a stored procedure to find out if the current user is a member of a certain role.

I want to pass the role name and return a bit to tell whether he is a member or not.

I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.

DECLARE @.usr varchar(32)

SET @.usr = USER

EXEC sp_helpuser @.usr


But if the current user is a member of more than one role it returns multiple rows. So if I could do something like:

DECLARE @.grpName varchar(32)

SELECT * FROM (EXEC sp_helpuser @.usr) WHERE GroupName=@.grpName
IF rowcount > 0 THEN
RETURN 1
ELSE
RETURN 0
END IF

I know that doesn't work so how can I do this?I'm sure that someone out there can do better than this, but you might try:

ALTER PROC spCheckGroup

@.UserName varchar(255), @.GroupName varchar(255)

AS

DECLARE @.Count int

SELECT @.Count = Count(*)
FROM (
select
s1.name as username,
s2.name as groupname
from
dbo.sysusers s1 left join dbo.sysmembers sm on
s1.uid = sm.memberuid
left outer join dbo.sysusers s2 on
sm.groupuid = s2.uid
where
s1.uid < 16383
) t1
WHERE
t1.userName = @.UserName and
t1.GroupName = @.GroupName

If @.Count > 0
Return 1
ELSE
Return 0

Test it with this code:

[/code]
DECLARE @.return_status int
EXEC @.return_status = spCheckGroup 'OAJO-SQLAdmin', 'db_owner'
SELECT 'Return Status' = @.return_status
[/code]|||check: BOL

IS_MEMBER ( { 'group' | 'role' } )|||I'll just crawl back under the rock where I came from...|||LOL.

Thanks for trying.|||I need a stored procedure to work with .NET so here's what I have:

CREATE PROCEDURE IsGroupMember
(
@.groupName nvarchar(32),
@.retVal bit OUTPUT
)
AS
SET @.retVal = IS_MEMBER(@.groupName)
GO

and in Query Analyzer I run this:

DECLARE @.bt bit

EXEC IsGroupMember 'db_owner', @.bt

IF @.bt = 1 print 'member'
ELSE IF @.bt = 0 print 'non-member'
ELSE print 'undefined'

but I keep getting undefined. What's wrong?|||EXEC IsGroupMember 'db_owner', @.bt output