I've been working on an issue where a single select within a stored procedure failed due to permission-issues, and we traced it to a call to a Synonym:

USE DatabaseA
CREATE SYNONYM [dbo].[DatabaseB_dbo_TableT] FOR [DatabaseB].[dbo].[TableT]

When debugging this, we set up a number of traces in the SQL Server Profiler to see what was going on.

DatabaseB, being the database holding the table that ultimately delivered the data has few connections, so we were able to set up the profiler without any other filters than the DatabaseName (and excluding the profiler's Application Name).

If I SELECT directly from TableT, my query shows up in the profiler for DatabaseB as expected.

But if I access the same data through the synonym, it only shows up in the profiler for DatabaseA - not for DatabaseB, which I would have thought.

Is this really as intended? While I haven't checked this with different users/schemas (experimenting with the auditing), it seems counterintuitive to me that I can access data in a database, without having this reflected in a profiler that is supposedly logging anything going on in the database.

I hope somebody can offer some insight to this.

  • I was going to answer that the profiler logs based on the connection, not the eventual data source (as this is how the stuff is logged in the sys. tables. I thought this was too simplistic so maybe someone has a deeper understanding of how the profiler works. Dec 14, 2011 at 13:27

1 Answer 1


The query is run in DatabaseA, not DatabaseB, so I'd say by design. That is, SQL Profiler captures "what is the connection database context" not "In what database is the object am I accessing"

This makes sense: each database is logically isolated from each other. Using a synonym or other cross database query is a special case because you can't ensure all databases are in synch (especially around restores) nor enforce foreign keys etc

An other example...

USE DatabaseD
  DatabaseA.dbo.TableA A
  DatabaseB.dbo.TableB B ON ..
  DatabaseC.dbo.TableC C ON ..

It runs in DatabaseD so this is what you'd trap in profiler

Workaround? As far as I know (I haven't tried though) you can't distinguish the database of an accessed object, only of the connection.

  • Thanks. Very good example - and as far as I can see (I'm not really strong on setting up audits), if you set up an actual audit on the table, the access will still be logged. (As it should be).
    – user5094
    Dec 16, 2011 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.