[dba-SQLServer] Finding current login group

More testing - I have written the following sproc -

         declare @qs nvarchar(4000)

         DELETE FROM ttmpTemporary

         SELECT @qs = '  INSERT INTO ttmpTemporary (F1)
                 SELECT Privilege
                 FROM openquery(localServer, ''exec master..xp_logininfo'')'

         EXEC (@qs)

When run from query analyzer I get the following message -

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'exec master..xp_logininfo'. The OLE DB provider 
'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider unable to process 
object, since the object has no columnsProviderName='SQLOLEDB', Query=exec 
master..xp_logininfo'].

Yet when I run "EXEC master..xp_logininfo" in Query Analyzer I get a result 
set with the columns expected in BOL.

This is starting to get urgent.

David


At 5/07/2004, you wrote:
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>