I am not a trained DBA, but I manage a software application that uses SQL Server for the back end. The applications manages "projects" and each project has its own database.
Because of the limitation in reporting through the application, we set up a generic SQL Server user account with read-only permissions to all the project databases. Users utilize that account to connect through ODBC and execute queries.
Now I would like to audit those connections – when they are happening and to which database.
Can anybody assist me with this?
I have administrative access to SQL Server itself. I have a very basic knowledge of SQL syntax. I am completely ignorant on the auditing capabilities of SQL Server, though.
Thanks in advance!
Best Answer
If you want to audit on an ongoing basis then a server side trace will do the job.
Below is how you will script out from Profiler
Entire SCRIPT :
If you want to just see who is connected now then you can use DMVs to look into
The NULL in dbid (below query) indicates that those queries are Ad Hoc queries.