SQL Database Access Control
Posted on 2016-09-20
I want to setup such a databse access control mechanism so that anyone in the team patches anything in live should be captured as to who patched what.
Currently there is no control in place and team shares DBO user credentials for database patching which can not be changed since it is used by application.
How can I restrict team from using dbo account for database log in and if someone does that how an alert can be setup or any other ways to handle that?
Re patches, one way I thought is to create Stored Procs for common / usual database patches, give execute rights to Stored procs to individual's windows login by removing updayes rights of their windows login so that they can update whatever SP allow them to patch?
Can I take the access control forward by restricting dbo usage and giving SP access to team or is there any other better way to hadle this area?
My organisation is very much concerned about the anonymous patching impacting business operations. Please advice?