question

RicardoOliv-7430 avatar image
0 Votes"
RicardoOliv-7430 asked TomPhillips-1744 commented

Logins Information History

Hi all,

I need some help on this:

  • i need to store the logins made overtime, including user, database, date, etc., for all the DBs of an instance.

Does anyone know some script to build a view or something else than can do this? To store all logins information at instance level?

TY :D



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered TomPhillips-1744 commented

Hi @RicardoAnselmoOliveira-7430,

To store all logins information at instance level?

Yes, you can enable SQL login audit to monitor SQL Server Database Engine login activity as below screenshot. You must restart SQL Server before this option will take effect. You can find the audit information from error log.

174670-screenshot-2022-02-16-110309.jpg

174771-screenshot-2022-02-16-110828.jpg

If you want to audit database level information, please refer to MS document Database Audit Specification. If you want to audit more information about SQL logins, such as get the information when login password is changed, you can enable server level audit LOGIN_CHANGE_PASSWORD_GROUP, please read MS document Server-Level Audit Action Groups to get more.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Be aware the "login auditing" above, does not return the database selected in the connection string. If this is required, you must use a login trigger.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

See "Use a SQL Server Logon Trigger for Auditing":
https://www.mssqltips.com/sqlservertip/6103/sql-server-logon-trigger-examples/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.