[dba-SQLServer] Finding current login group

OK - getting closer.

I have a procedure in my FE like this -

     Dim cmd As ADODB.Command, strCurrentUser As String, rst As ADODB.Recordset

     'This returns the user (eg egas\david)
    Set cmd = basGetCommand("dbo.spGetCurrentUser", adCmdStoredProc)
     cmd.Parameters.Append cmd.CreateParameter("@CurrUser", adVarChar, 
adParamOutput, 100)
     cmd.Execute
     strCurrentUser = cmd.Parameters("@CurrUser")

     Set cmd = basGetCommand("master..xp_logininfo", adCmdStoredProc)
     cmd.Parameters.Append cmd.CreateParameter("@acctname", adVarChar, 
adParamInput, 100, strCurrentUser)
     Set rst = cmd.Execute
     basGetCurrentRole = ""
     If Not (rst.BOF Or rst.EOF) Then 'Record exist        <=  Error occurs 
here
         rst.MoveFirst
         While Not rst.EOF
             'Users should only belong to 1 login group
             If Not IsNull(rst![Permission Path]) Then basGetCurrentRole = 
rst![Permission Path]
             rst.MoveNext
         Wend
     End If

When this is run I get an error on the line above where indicated.  The 
number is 3704 - Operation is not allowed when object is closed.  Why 
should it be closed when I have just opened it?

Billy's code below works ok if run from Query Analyzer.  I have given the 
users permissions to execute master..xp_logininfo.

David

At 4/07/2004, you wrote:
>xp_logininfo sproc is in master db... so try...
>
>/* -- cut here */
>DECLARE @sys_usr varchar(100)
>SET @sys_usr = SYSTEM_USER
>SELECT @sys_usr
>EXEC master..xp_logininfo @sys_usr
>/* -- cut here */
>
>be aware of the execute permissions of this sproc...from bol, it reads:
>
>Execute permissions for xp_logininfo default to members of the db_owner 
>fixed database role in the master database and members of the sysadmin 
>fixed server role, but can be granted to other users.
>
>HTH
>
>Billy