March 14, 2013 at 12:18 pm
I'm trying to use CDC for auditing certain tables, the problem I find is it doesn't capture the user.
I have tried using Default Constraints SUSNAME_USER and adding an extra column to the cdc table. But it puts sa into the field each time is there another way to do this using CDC. A part from changing the user table to capture this. I can't see another option.
March 14, 2013 at 1:15 pm
Bobby Glover (3/14/2013)
I'm trying to use CDC for auditing certain tables, the problem I find is it doesn't capture the user.I have tried using Default Constraints SUSNAME_USER and adding an extra column to the cdc table. But it puts sa into the field each time is there another way to do this using CDC. A part from changing the user table to capture this. I can't see another option.
CDC will capture the user name that made the connection. This is not the same as the user that is logged into your application. BTW, You really should not be using the sa account from applications.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2013 at 1:46 pm
Thanks. Not using sa from the APP. Only when I update via SSMS it captures as sa.
March 14, 2013 at 1:50 pm
Bobby Glover (3/14/2013)
Thanks. Not using sa from the APP. Only when I update via SSMS it captures as sa.
Gotcha. Change the user you login to SSMS with your captured data will have the new login.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2013 at 5:44 am
Ok if the base table captures the user who made the changes and the time, is CDC a suitable vehicle to be used for auditing.
March 15, 2013 at 5:54 am
It depends on how many other users are using a generic login like "sa".
The audit results will only be as good as what goes into it.
If app connections are made with a service account for example, it will be difficult to distinguish which specific user dis something.
Same goes for SSMS connections, if users are connecting with sql-based logins.
March 15, 2013 at 5:59 am
...
If app connections are made with a service account for example, it will be difficult to distinguish which specific user dis something....
It's quite common design when DB connection is made via service account.
One of the ways I personally like to deal with such thing is to use Context_Info to as a mechanism to pass NTUserId from client to SQL server.
So, any modification in the database will be rejected if the current Context_info doesn't contain NTUserId...
March 15, 2013 at 7:06 am
Base table captures NTuser name (domain name). Never captures sa. 'sa is a red herring from the previous post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply