1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Travis
  • 13
  • 3

1 Answers1

0

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

Community
  • 1
  • 1
Slava
  • 1,065
  • 5
  • 11
  • I see. So is there no way to find out who is accessing what database during a fully qualified table name? Right now the permissions are not as tight as they should be and we want to lock down the database access to a more focused security group. The easiest way I thought would be to audit the database access for a week and then find the most common security group those users are a part of. – Travis Apr 21 '14 at 19:40
  • looks like it is possible, but not easy. [This is what I found](http://stackoverflow.com/questions/296906/log-table-access-using-sql-server-profiler/326980#326980) – Slava Apr 21 '14 at 20:49