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)