Dealing with security violations is 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

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 .