[dba-SQLServer] Finding current login group

Because openquery requires a string (see BOL).

David

At 5/07/2004, you wrote:
>Why do you have pairs of quotes around the 'exec master..xp_logininfo'
>argument? That may be the problem.
>
>Arthur
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
>Emerson
>Sent: Sunday, July 04, 2004 7:26 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [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
> >
> >
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com