Checking permission rights using SQL Server Stored Procedure and return values

[Scott]

With advice from the usegroup, I was able to solve a problem I had.
(Specifically, my thanks to Doug Steele, who is an invaluable resource
on this usegroup.) I thought I would share my result, in case it helps
someone.

I use permission groups from our corporate security setup to control
access rights to data in my adp. I needed a stored proceedure that
would check if a user is a member of a group. Here is what I've come up
with. I hope it helps someone in the future

This is the VBA code, with the T-SQL stored procedure referenced as a
comment:
'---------------------------------------------------------------------------------------
' Procedure : fctCheckGroupMembership
' DateTime : 10/1/2006 08:39
' Author : Scott
' Purpose : Check membership in a global group using
' SQL server's access to group permissions
' Inputs : Requires the group name, using format
' [domain]\[group name]
' Outputs : Function returns 0 if user is NOT a member of the group
' Returns 1 if the user IS a member
' Returns 3 if the group cannot be found
' Requires : SQL Server stored procedure named uspCheckMembership.
'
' ***************uspCheckMembership*************************
' --Script Date: 10/1/2006 8:18:09 AM
' --Name: dbo.uspCheckMembership
' --Purpose: Checks to see if the user running the stored
' -- procedure is a member of the requested group
' --Inputs: Requires the @Group parameter.
' -- This should be passed in the format:
' -- [DOMAIN]\[USER GROUP]
' --Outputs: Returns 0 if user is NOT a member of the group
' -- Returns 1 if the user IS a member
' -- Returns 3 if the group cannot be found
'
' CREATE PROCEDURE dbo.uspCheckMembership (@Group varchar(60))
' AS
'
' DECLARE @IsMemberStatus as varchar(20)
' SELECT @IsMemberStatus = IS_MEMBER(@Group)
'
' IF @IsMemberStatus IS NULL
' Begin
' Print 'No Such Group'
' RETURN 3
' End
' Else
' Begin
' PRINT @IsMemberStatus
' RETURN @IsMemberStatus
' End
' GO
'

Function fctCheckGroupMembership(strGroupName As String)

Dim conCurr As ADODB.Connection
Dim cmdCurr As ADODB.Command
Dim intReturnValue As Integer


'On Error GoTo fctCheckGroupMembership_Error

Set conCurr = CurrentProject.Connection


Set cmdCurr = New ADODB.Command
cmdCurr.ActiveConnection = conCurr
cmdCurr.CommandText = "uspCheckMembership"
cmdCurr.CommandType = adCmdStoredProc
cmdCurr.Parameters(1) = strGroupName
cmdCurr.Execute
Debug.Print "fctCheckGroupMembership returned " &
cmdCurr.Parameters(0)
intReturnValue = cmdCurr.Parameters(0)
fctCheckGroupMembership = intReturnValue

On Error GoTo 0
Exit Function

fctCheckGroupMembership_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure fctCheckGroupMembership of Module modCheckGroupMembership"

End Function