My problem is that when using multi server queries using SSMS from SQL Server 2008 R2 against SQL Server 2008 R2, 2005 and 2000 I occasionally get error messages such as:
SQL Server 2008R2
Login failed for user 'DomainName\ADName'. Reason: Failed to open the explicitly specified database. [CLIENT: 18.104.22.168]
Error: 18456, Severity: 14, State: 38.
SQL Server 2005
2013-10-10 15:55:34.28 logon Login failed for user 'DomainName\ADName'.
Error: 18456, Severity: 14, State: 16.
SQL Server 2000
Login failed for user 'DomainName\ADName'. [CLIENT: 22.214.171.124]
Now my query works fine but given what my query does is search for errors in logs it is not ideal that it should also generate such errors.
My methodology may be flawed, what I have done is create a group under Local Server Groups and registered a few dozen servers of various versions. Then I right click on this group, select New Query to open a new batch window. Lastly I open my saved script and run it. In rough outline this is what it does
use master go determine version of SQL as like '%8.0%' or not create temporary table based on version and pump results of xp_readerrorlog 0-6 into it translate temporary table to another and insert to results temporary table select on results table drop temporary tables go
I am a sysadmin on all machines. Two things I tried was the 'use master' statement at the top of the script and changing the registered servers to have a "Connect to database" of master. Neither of these has eliminated the problem. Does anyone have any further suggestions?