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:]
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:]

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
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

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?

  • have you checked the sql server error logs on the server that's throwing the failure? – swasheck Oct 10 '13 at 15:21
  • @Paul What is the default database for the login you are trying to use ? – Kin Shah Oct 10 '13 at 15:22
  • @swasheck I thought that was what I quoted in the original question? Have I glossed over something? – Paul Oct 10 '13 at 15:27
  • @Kin on each server in question there is a Login named Admins. This is an AD group to which I am a member. That Login is a sysadmin with a default database of master in each case. – Paul Oct 10 '13 at 15:28
  • 1
    @paul we have told you what might be the issue. And you confirmed that everything is configured/checked OK. As we cant access (and we dont want), the only way to find out is running a PROFILER. This article will help you (mssqltips.com/sqlservertip/2581/…) choosing the events. That should solve your issue. – Kin Shah Oct 10 '13 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.