Hi,
I am bunch of error messages getting in my sql serror log: Login failed to user " " Reason: Failed to open the explicitly open the specified Database 'DB' [Client: <Local Machine>]
I tried to troubleshoot as it's a Error: 18456 and State:38.
I found that one of my database is is not exists and other user error is due to database is offline.
Database is not exists or offline so it's not showing under user mapping but somewhere its keep trying to connect that not exists or offline DB.
How can i find that this user from where trying login as it says Local Machine?
I want to stop this error message so need to know how i can as this user is tied with other database but login failed to which Db is not exists or offline DB.
Keep in mind that i can't drop user as these users are mapped to another Databases too.
Run this query to see what the default database is for the user(s)
USE master
GO
SELECT name , dbname FROM sys.syslogins
GO
You can change the default database from the user interface (object explorer -> Security - > Logins, right-click on the login name, select properties, and in the general tab, second from the bottom).
Or you can do that via script
ALTER LOGIN [LoginnameHere] WITH DEFAULT_DATABASE=[master] -- or another database name
In that case, it could be that the users (either directly or through code) are trying to access the off-line/non-existent database. The connection string may be specifying the "Initial Catalog" as the non-existent/off-line database.
I can't think of another possibility. Hopefully someone else has more ideas on why this happens.
Sql server Error log, looks like something running every minutes as i can see every minuted there is one entry of failed login for specific Login for specific DB.
I am trying to get the reports from jobs or other sql error report query to find out from where it's generating this error, i mean any job or any application or any program or any linked server but no luck.
Can you briefly run sql profiler. And have it dump into a database? Also have you recently upgraded ssrs? Changed password on a service account? Or disabled a service account?
Thanks Yosiasz.
I am new but it might be they have upgraded or disable the service account but database is offline so i am trying to search the root.
I just trying to find out if any DMV sql way can get the information or need to go through manual process? which will give me the information even though it's used in application as a connection string or as data source.