18 October,2012 by Jack Vamvas
The missing piece of the puzzle is a managed process to address non-compliance of the security policy. What steps are in place to report, review and take any necessary actions?
In a large Database Server environment this can be a resource intensive , as you may need to liaise with application owners , advising on appropriate privileges
These are some of typical questions and considerations of a regular Audit Process. Hopefully some of the ideas presented will assist in SQL Server - Security Risk Analysis and database security
What is the purpose of Database Server Security Audit?
A managed process to flag and address non-compliance of Database Server security policies
1) To check that all Databases are valid.
2) To check that there are no Active Directory(AD) users with direct name ref on the server/Database.
3) Database security is done with the use of AD groups or SQL Roles & Remove the use of unauathorised elevated privileges
4) Any exception has an exceptions report created.
5) The Owners have confirmed that all AD groups are valid and the users are current.
6) Any other details in the SQL Security Security Policy
How often will the Security Audit occur?
A report is generated and sent to the Database Server Instance owner every xx months. Read more on Powershell sql server security audit
What details are in the Security Audit Report?
There are five worksheets in the Report.
1) Every database level user and the privilege. Elevated Privileges are highlighted – and need some consideration
2) Every Database Role attached to a database user
3) Every database and every user attached . Similar to worksheet 1 – from a database perspective
4) Active Directory Reference of every database user inc: Name | Dept | Network ID | Db User Act
5) Server Roles – Every Logon with a Server Level privilege
What is expected from the owner?
1) The owner is expected to review the security details. The report highlights elevated privileges. They are a priority.
2) AD reference worksheet – Review the users in the AD reference worksheet. If there are users who should not have the level of access detailed, either a) consider changing the level of privileges b) Drop the user from the AD group
3) Send acknowledgement to the DBA Group – that you’ve reviewed the document and sign –off on the users. The DBA Team will record this acknowledgement and store with the report.
What if I want to maintain elevated privileges?
If the right(s) needs to be retained and there is a business - process justification , a Database Exception Request must be completed, otherwise the role will be removed from the logon account. This is to satisfy reulatoru audit requirements, and would fulfill the requirements of the SQL Server security policy.
Contact the DBA Team for an Exception Request Form.
The steps for exception are:
1) Complete a form from
2) Attach the form to a helpdesk request - this will go through a review
3) Once the particular sql server instance is signed off -