Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Jon Egerton Dec 14 '11 at 13:27
add comment

migrated from stackoverflow.com Dec 14 '11 at 13:42

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '11 at 13:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.