Need to find Users logging Database in a week

Question

There is a request to capture users and service account logging for particular Database and to send report in excel to the management. Requesting all to give a script if you have already or an idea how this can be accomplished.

Answer 1

Hi Allen, how are you?
Here is a simple script that maybe can help you.

The same uses a dynamic management view of the SQL server

`USE [<dbname>] ---> Database Name

go

SELECT DISTINCT login_name

FROM sys.dm_exec_sessions

WHERE last_request_start_time >= DATEADD(week, -1, GETDATE())`

Answer 2

for particular Database

Users/Accounts login to SQL Server, not to database. During the session they can change the context to the one or the other database. Or they use three-part qualifier to query data from different databases then the current one.

In other words: It's not really possible.

Answer 3

I this is the same problem as your question? You are moving a database on this instance to a new instance? Or is it an unrelated request?

I think you look at SQL Server Audit. I have not used it a lot, but you should be able to audit database access. But Olaf lists a couple of caveats that are relevant.