Sql-server – Is execution of synonyms not logged in target database

sql serversql-server-2008

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
GO
CREATE SYNONYM [dbo].[DatabaseB_dbo_TableT] FOR [DatabaseB].[dbo].[TableT]
GO

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.

Best Answer

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
GO
SELECT *
FROm
  DatabaseA.dbo.TableA A
  JOIN 
  DatabaseB.dbo.TableB B ON ..
  JOIN 
  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.

Related Question