I am trying to audit our security for our SQL Servers. I am trying to run a trace on a database to get the users that logon and access the database. However if the query is ran from a different database there are no logon events generated.
Example: I am trying to trace logons for [Database2]
Use [Database1];
Go
SELECT * FROM [Database2].[dbo].[Table]
But there are no events logged in the trace under Database2.
Our environment is SQL Server 2008 R2 Standard Edition. Any suggestions would be appreciated.
This is because database users don't login to SQL Server. SQL Server has security entities called "logins" that are used to login to an instance of SQL Server. These logins are either Windows logins from Active directory or SQL Server logins, that you can define and specify user name and a password. Database "users" are defined at database level. Users don't login to anywhere, they don't even have a password. The idea is that SQL Server "login" is mapped to a specific database "user" when connecting to a specific database. Your query to a different database doesn't generate a login, because logon is a server-wide event, not a database-wide.
For example see here