How to Log Successful Login Attempts to a Database in SQL Server

sql serversql-server-2008

Is there a way to log database name when auditing logins in SQL Server (2005 and above)?

I basically want to log the username and the database name the user successfully connected to.

I have tried profiler, databasename column is blank.

Best Answer

Your best bet is to use a Logon Trigger

Just make sure your trigger captures SYSTEM_USER and ORIGINAL_DB_NAME() (this is only available in SQL 2008 +).

Just be careful with logon triggers. If you mess them up you can prevent any logins, because when they fail they cause the login to fail and then you have to use a workaround to get into the system and drop the trigger so you can fix it.

Don't let that hinder you though, just know you need to test them out thoroughly before you go throwing them onto a production server.

Related Solutions

Sql-server – Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server

Unfortunately, the group is not presented in the LoginName column => it is the individual user account (DOMAIN\user).

You can get the groups for a given member by executing:

EXEC XP_LOGININFO 'domain\groupname','members'
EXEC XP_LOGININFO 'domain\groupname','all'

This may be able to be wired up using a user-defined event.

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

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.

Best Answer

Related Solutions

Sql-server – Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server

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

Related Question