1

I have an application that accesses my SQL Server using a username\password. However our lead developer has access to this password and I suspect that he may be utilizing this outside of our terms of agreement. My wish is to audit for anytime this username (appaccount) accesses our database and any commands that are issued.

Luckily this application uses purely stored procedures with passed in parameters when it accesses the database so anytime the account runs a T-SQL statement it has to be from our developer in question.

My ideal output would be something like this:

Datetime | Username | Action Performed

11:23am | appaccount | "Select * from claimstable"|

11:26am | appaccount | "update table ...(skip change control process)"|

3
  • 1
    What version & Edition of sql server you are using ? You can look into server side trace, auditing or extended events to do the job.
    – Kin Shah
    Nov 24, 2015 at 17:56
  • 1
    Just because the application does all data access through stored procedures doesn't always mean that the role that's being used by the application has been restricted to only executing stored procedures. It might be worthwhile to double check the permissions for any role the developer in question has access to.
    – Erik
    Nov 24, 2015 at 18:03
  • 1
    Based on a recent edit attempt on your question it appears as if you've created multiple accounts. Please merge your accounts and you'll be able to edit your question freely.
    – Erik
    Nov 24, 2015 at 18:47

1 Answer 1

0

You have to try this using SQL Auditing. However, if you're after only a log of when the user has accessed the database in general (like when they logged in), you can use the default database Login trace, which records both Successful and Failed logins, to get at least an idea of when the user went in. You'd have to then ask them as to why they logged in!

enter image description here

The log of this is in the Error Logs under SQL Server Agent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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