Regular SQL Server scans for security violations is key to maintaining a Security Policy. An effective method is to create a SQL Server Security Violations Report.
What is the purpose of SQL Server Violations Report?
A managed process to highlight non-compliance of selected SQL Server Security Policies. The details differ amongst organisations but the basic principle remains consistent.
Typically a report is created through a scheduled scan of SQL Server Instances. It makes sense to prioritise Production but lower environments also require attention
The security scan is managed by the DBA . The DBA usually maintain reports on different aspects of SQL Server administrations . Some examples are:
How often will the SQL Server Violation Report be generated?
Dealing with security violations can be a time-consuming task. Depending on your circumstances , I’d advise to generate a report once a week.
Generating a daily report , unless required, can begin to dominate the day and distract from other important tasks
What details are in the SQL Server Violation Report?
This is an example of the sort of violations to check. There are all sorts of possibilities and is defined by requirements. Advice: Start of small and clear up the easy ones before moving to obscure violations
a) All non – AD logins which do are not accounted through the exception process
b) All instances of permissions granted directly to an object and not through a database role
c) If the login is a SQL based login return the login if the password is blank
d) If the login is a SQL based login return the login if the password is null
Read more on Find Weak passwords in SQL Server - SQL Server DBA
What is expected from the DBA ?
The DBA will :
a) Review the Violations Report and work with the owners to either fix the issue or if a valid business justification is given – apply for an security exception.
b)The issue fix will be managed via an auditable communication trail. For example , a helpdesk request system .
What if the owner wants to maintain the security violation ?
This is the fun part! If the security violation needs to be retained and there is a business - justification , some sort of Database Exception process must be completed, otherwise the violation should be be removed from the logon account.
Documenting and implementing the exception process is an important step. Firstly , it illustrates to security officers and auditors you are managing security . Secondly, it becomes a task list , which you can use as a reminder to fix the issues .
Read More on Database server security audit process