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)"|
-
1What 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– Kin Shah2015年11月24日 17:56:16 +00:00Commented Nov 24, 2015 at 17:56
-
1Just 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– Erik2015年11月24日 18:03:15 +00:00Commented Nov 24, 2015 at 18:03
-
1Based 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– Erik2015年11月24日 18:47:03 +00:00Commented Nov 24, 2015 at 18:47
1 Answer 1
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!
The log of this is in the Error Logs under SQL Server Agent.