question

Neo avatar image
Neo asked

Capture login and access to a specific database

Hi,


Is there a way that I can capture user access to a specific database, into a table perhaps. I'm just looking to record date and time of login and which database is accessed.;


Any help would be appreciated.


Thanks

sql server 2016
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
torgerud avatar image
torgerud answered

There may be a way to query this, but I don't know what that is. I capture login activity from SQL Profiler events.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Neo avatar image Neo commented ·

I would normally do it this way, but I need to capture the data over a long period, about a week

0 Likes 0 ·
JohnM avatar image
JohnM answered

Depending on what version of SQL Server you are using you can use an Audit to track this information. You can also use extended events to watch for locks on the database. If there's a lock then they touched the database. If you want specific tables, using an Audit is probably better suited here.

Reference - https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Neo avatar image Neo commented ·

Hi JohnM,


Many thanks for your response. I am using SQL Server 2012 Standard edition, is there a way I can do this on that version?


Thanks

0 Likes 0 ·
JohnM avatar image JohnM commented ·

Unfortunately I don't believe so. You can do server level audits (I think) but database level audits required Enterprise. I would look towards extended events to track this. Or upgrade to SQL Server 2016 SP1 or higher for database audits

0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

I believe you can do database level login triggers even in the standard edition.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.