Capture login and access to a specific database

Question

Question by Neo · Feb 05 at 08:16 AM · sql server 2016

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

0

10 |1200 characters needed characters left characters exceeded

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

Answer 1 · 2021-02-21T04:32:58Z

Answer by Jeff Moden · Feb 21 at 04:32 AM

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

1 · Share

10 |1200 characters needed characters left characters exceeded

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

Answer 2 · 2021-02-06T00:45:15Z

Answer by JohnM · Feb 06 at 12:45 AM

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

0 Show 2 · Share

10 |1200 characters needed characters left characters exceeded

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

Neo · Feb 08 at 06:44 AM 0

Share

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

JohnM · Feb 08 at 07:27 PM 0

Share

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

Answer 3 · 2021-02-05T14:49:52Z

Answer by torgerud · Feb 05 at 02:49 PM

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

0 Show 1 · Share

10 |1200 characters needed characters left characters exceeded

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

Neo · Feb 05 at 03:01 PM 0

Share

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

Capture login and access to a specific database

3 Replies

Your answer

Follow this Question

Related Questions