Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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?

share|improve this question
    
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 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 Oct 10 '13 at 15:47
show 10 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.