Failed to open the explicitly specified database - but what database was it?

A situation where SQL is helpful, but not quite helpful enough, is when it reports that a user is unable to access a certain database.

SQL will now helpfully tell you the user - it will even give you the IP address of the machine they tried to connect from - leaving you short of one minor detail ... which database was it they were trying to access.

This will show up in the error logs as something like:

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Grumpy\DBA'. Reason: Failed to open the explicitly specified database. [CLIENT: 11.22.33.44]

So what was the specified database??

I'm not sure if there is a way to retrospectively find this out, but you can certainly use Profiler if it is something that is still happening.

In profiler, the Eventclass that gives you the magic information is under "Errors and Warnings", and is the "User Error Messages" Event.  That's right - SQL tells the user what database they can't open but won't tell you!

If you also enable the "Security Audit", "Audit Login Failed" events, and add a filter against "LoginName" for the user name in the original message.

What you should see are "Audit Login Failed" messages for the login you care about, that give the same message as in the Error Log, but you should also see a "User Error Message" entry just before it, and in here you get:

Cannot open database "GrumpyDatabase" requested by the login. The login failed.

The ApplicationName field may also help identify what the user is trying to use to access this database, but at least now you are armed with all the relevant details when you challenge them about trying to access your databases.