OPENQUERY and server permissions

Question

when I run my stored procedure it is a complete success, even if I run it through exec.

Running these:

    sp_GetServerLogins
    GO
    exec ( 'sp_GetServerLogins ')
    GO

I get this:

Needless to say this first:

    select @@servername

    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'     
                        ,@optvalue =  'TRUE'

Now when I run the same stored procedure, on the same server, using OPENQUERY, this is what I get:

        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) + 
',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

    exec ( @sql)

If you haven't found the difference is in the Login_From.

Basically what I am trying to achieve here is to find windows_logins that belong to a windows_group login that are also present in the same server.

Inside my procedure the critical line that get the windows group for an individual login is the following:

INSERT INTO @UserList EXEC xp_logininfo @LOGIN, 'members'

The funny thing is that I have servers where these two (running the procedure through exec AND running the procedure through OPENQUERY) return the exactly same thing. That is great.

Some other servers, however, give me the results above.

What is missing? any ideas?

cheers Marcello

P.S.

I need to use OPENQUERY because I want to achieve something like this, on the code below:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )


        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO #Radhe
        exec ( @sql)


        SELECT * FROM #Radhe

Basically I have my data inside a temp table, it could be in fact a table variable.

If I do without the OPEQUERY:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )

        INSERT INTO #Radhe
        exec sp_GetServerLogins 

I get the error below:

Msg 3930, Level 16, State 1, Line 11 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

when I use the function fn_my_permissions, I get the exact permissions inside and outside the OPENQUERY.

                declare @sql nvarchar(max)
                select @sql = 'SELECT * FROM OPENQUERY(' + 

quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; SELECT * FROM 

fn_my_permissions(NULL, ''''SERVER''''); ' + '''' + ')'
                exec ( @sql)

Answer 1

You asked for ideas--here's mine.

We had similar issues. On SQL Servers where the issue exists try changing the SQL Server service account to a Windows account instead of a built-in account, restart SQL Server, and retry the query.

If you're running these queries with SQL Agent, you may need to change the SQL Agent account to an actual Windows account.

My thought is that build-in accounts can't access Active Directory properly.