How to checks all system and user-defined functions, triggers being modified. Thanks
how many developers do you have and do you have a system of source control (github, gitlab) and deployment method (dev, qa, staging, production)?
Who has permission to make changes to sql artifacts (functions, procs, views, triggers)
I am STIG the SQL server, there is STIG require to have a job to monitor the function, or trigger being modified by unauthorized user
they should not be any unauthorized user modifying any sql objects. but if you do need to maybe implement some sort of trigger that collects that changes that data. but if there is access by unauthorized user, they could disable that trigger.
how to create a trigger to collect info when a function is modified? I only know how to create a trigger when data is inserted, update, delete to a table
But why create a trigger? Why not implement some change control.
Check out check in code code review and deploy only approved code
Your source control becomes you audit trail
You might consider adding a DDL trigger to your server/database.
A DDL trigger can be at the database level, or at the server level, and can fire in response to specific events on functions (e.g. ALTER_FUNCTION) or more generally DDL_FUNCTION_EVENTS. Search for more detailed info on DDL triggers. You can tailor the trigger to fit your needs.