0

I'm using SSMS's Audit feature to see who's successfully logging in and logging out of the server and I'm currently testing it in a server in which only I have access to it. I used for SUCCESSFUL_LOGIN_GROUP and LOGOUT_GROUP for my server audit specifications.

Every time I view the audit logs, why is it that I see that my account constantly logging in and out of the server when in reality I didn't even log out?

1]

1

It looks like you're using SQL Server Management Studio (SSMS) to query your server. SSMS will open one session for your query tab, plus another session to populate the Object Explorer.

By default, SSMS also has IntelliSense enabled. IntelliSense will make a high volume of very quick database queries in order to validate your query against database schema metadata. Each of the queries will involve a login to the server. I suspect this is responsible for the majority of what you are seeing. One quick way to verify is to disable IntelliSense on your SSMS. Alternatively, you could run you test from another tool that is less "chatty" with the database (such as the SqlServer PowerShell module or sqlcmd CLI).

  • Yes, I am using SSMS to query. Disabling IntelliSense actually got me similar results as to viewing the logs through the object explorer way. But I want IntelliSense to be on when I work. by what do you mean by "run your test from another tool"? Is it like running the audit on a different tool? – Roxzurafa Nov 20 '18 at 1:45
  • 1
    Auditing is configured with SSMS, but the audit runs at the server on the database. Audit isn't running within SSMS. – AMtwo Nov 20 '18 at 2:44
  • SQL Server cannot change how a client application behaves. If the client app keeps connecting and disconnecting all the time, then the audit will catch that - you don't want the audit to lie to you. Try using sqlcmd.exe to do something in SQL server and I guess that you will only see one connect/disconnect. – Tibor Karaszi Nov 20 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.