Manual Database Table update security issue

Hello,

While it may not be a desirable solution, you can revoke all DML access on tables and grant access only to stored procedures. Within stored procedure it is easy to get current logged on user and log the details and even choose not to update.

Also side note on Kapil's post - CDC feature is available only in Enterprise Edition of SQL Server.

Hope it helps!

Kapil Singh Kumawat4y ago

in sql server you can enable change data capture which will tell you what values has been changed.

or

you can also create a trigger on that table that will do action on update

Muhammad Imran Ansari4y ago

There is no built-in functionality to maintain log. You need to create a after update trigger like the following:

CREATE TRIGGER [schema_name. ] trigger_name ON table_name

AFTER UPDATE

AS

BEGIN

[SET NOCOUNT {ON/OFF}] {SQL statements}

END

You can also get the script to achive this through following link:

https://docs.microsoft.com/en-us/answers/questions/162506/sql-server-how-to-find-out-who-update-table-data.html