Most DBAs will have a SQL Security Security Policy , outlining  what is acceptable security standards for physical and logical access.

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

 Details:

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 -

 Read More

How to create a SQL Server Security Audit

 Powershell sql server security audit

 SQL Server - Powershell and Failed Logon attempts