All In One SQL Server Security Audit Script

As a DBA, Secure my SQL server is a pretty important part. Generally Security in the sense most of us point to users and their weak passwords. But apart from user accounts, there is some critical part are also there.

Instead of google it and execute all the queries which are found in many blogs and combine all the reports together is not a feasible way, So I have been taken a list of security checklist and prepared a Tsql script to check all the loopholes in the SQL Server.

Checks:

1. SQL services account - All sql services must run under an AD account or Administrator account

2. Default directories - Data,Log and backup directories must not be in C:\ drive

3. Startup Type    - SQL serices should be start automatically

4. SA Account name   - Its a best practice to rename SA account 

5. Disable SA account - Create an alternate SQL user with SYSADMIN priileges and disable SA account

6. Password Check   - Change SQL users password at every 3 months, 

             Don't make Username and password is same, 

             Don't user blank passwords

7. SysAdmin User    - List of users who have sysadmin role.

8. SQL Port Type    - SQL is using Static Port or Dynamic Port

9. SQL Port      - Use any port other than 1433.

10. Number of databases - Use 100 or < 100 databases for a server.

11. Buildin Administrator - Disables Buildin\Administrator group from sql login. 

12. Database level Access - Limit the db_owner users.  

Link to the script: