October 19, 2010 at 10:38 pm
Hi,
I am trying to query similar structured databases on multiple servers. Although I have many reasons to do this, my current need is to get a full list of permissions for each object in all the databases and compare across the group.
I have the same permissions on each of the databases and can run the same query on each of the databases. Some servers have more than one database, so I can run the query on multiple databases so long as they are on the same server.
So far, the only way that I can query multiple servers is via a vba script in Excel that makes the connection (using ADO), then runs the same query. I have been looking around for the last few hours trying to get a solution that will work just using the SQLSERVER and cut out Excel.
Servers aren't linked. They are all on the same domain. I am in dbo groups for all. All databases have Windows Authentication.
I tried using OPENROWSET:
SELECT '<databasename>' AS db, a.*
FROM OPENROWSET('SQLNCLI', 'Server=<servername>;Trusted_Connection=YES',
'SELECT * FROM <databasename>.dbo.<tablename>') AS a
but get the error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Not really sure why it is using anonymous logon when I am logged in with Windows Authentication.
What am I doing wrong? Is there a better way?
JK
October 19, 2010 at 11:23 pm
The easiest way query multiple servers using GUI is the SSMS in SQL Server 2008. But it has limitation that it cannot be scripted or run using a job. You may give it a try. Here[/url] is an example.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply