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).
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.