November 9, 2011 at 2:22 pm
Want to thank everyone again for helping me with the OPENQUERY syntax problem.
Now that the query is working, I am puzzeled by the data being returned.
A table containing all the user logins for a server is being populated. This info is being extracted from master.sys.server_principals via the openquery.
The procedure is looping thru a list of servers from our ‘server database’ and using that data for the parameters needed in the openquery to link to all the servers. We have about 50 sql servers. Half are Kerberos and the other half use NTLM authentication.
Reviewing the ‘user_logins’ table after the procedure executed, I noticed some logins were missing. The servers which use NTLM authentication listed only the SQL_LOGIN logins. The servers using Kerberos authentication listed SQL_LOGIN and both windows logins (WINDOWS_LOGIN and WINDOWS_GROUP).
Has anyone ever seen this? (yes, the NTLM servers have WINDOWS logins).
Thoughts, ideas, suggestions?
Thx
John
OPEN HOST_Cursor
FETCH NEXT FROM HOST_Cursor INTO @LinkedServer,@port_nbr
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'INSERT dbo.SERVER_LEVEL_Access
SELECT *
From OPENQUERY ([' + @linkedServer + ',' + @port_nbr + '],
''SELECT
@@servername
,name
,type_desc
,convert(varchar(10),create_date,121)
,convert(varchar(10),getdate(),121)
,null
,null
FROM master.sys.server_principals
WHERE type_desc not in (''''SERVER_ROLE'''' , ''''CERTIFICATE_MAPPED_LOGIN'''') '') '
EXEC (@SQL)
FETCH NEXT FROM HOST_Cursor INTO @LinkedServer,@port_nbr
END
November 10, 2011 at 3:38 am
There seems to be problem with the permissions rather than the query itself. You can easily identify it by fetching the server principals through connecting to the servers with a sysadmin user.
November 10, 2011 at 9:35 am
Thanks! Had to think about this for a bit but I tracked it down to a special login we use just for NTLM servers. Changed the permissions and it is now working.
November 10, 2011 at 10:17 pm
Glad to hear that. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply