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.

What reports are being read in SSRS

If you want to check which reports are being run (or not) in SSRS, or details about which users are running reports, you can pull this information from the ReportServer database.

If you need to check you are on the right database, you can check the SSRS machine name and instance the ReportServer database relates to by running:

SELECT
    MachineName,
    InstanceName
FROM Keys
WHERE Client = 1

Now to find out who's being doing what ... and SQL keeps it all nicely for us in the table "ExecutionLog".

You need to join this back to Catalog to get the report names / paths, but you can then easily start to get information on the most frequently run reports, which users are running certain reports, or even which reports are slow to render.

E.g. to get details on how often a report is run:

SELECT c.Name, c.path , count(*) as 'ReportRun'
FROM ExecutionLog e 
JOIN Catalog c on e.ReportID = c.ItemID
GROUP BY c.Path, c.Name

to find out which users are running a certain report:

SELECT userName, COUNT(*) 
FROM ExecutionLog e 
JOIN CATALOG c ON e.ReportID = c.ItemID
WHERE c.Name = 'ReportName'
GROUP BY e.UserName

I'm sure you can find other details to pull out of this table that may prove useful.

NOTE!

By default the retention period for the ExecutionLog table is 60 days.  However this is also adjustable by updating the ConfigurationInfo table for the ExecutionLogDaysKept row.  Set the number of days, or use "0" to keep data indefinitely.

E.g.
UPDATE ConfigurationInfo 
SET VALUE = '-1' 
WHERE Name = 'ExecutionLogDaysKept'

NOTE - Ignore books online which says this should be set to 0 - if you look at the clean up proc in the ReportServer database, namely ExpireExecutionLogEntries, you can see it actually only leaves the data if the value is set to -1, and not 0.

I would also recommend you download the SSRS Reports Dashboard.  This plugs into SSMS and once installed, you just right click on a ReportServer database, and it gives you access to all sorts of info relating to the relevant SSRS instance.