Scribd
Upload
83 views2 pages

Comprehensive SQL Server Security Audit

The document describes an all-in-one SQL script that audits security across a SQL Server. It provides a checklist of items to audit, including the service account, default directories, SA account configuration, passwords, roles and permissions. The script consolidates checks for these items and outputs server-level and database-level audit results in one place, providing a more feasible way to audit security than searching blogs for individual queries. A link is provided to download the script.

Uploaded by

rajiv_ndpt8394
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
83 views2 pages

Comprehensive SQL Server Security Audit

The document describes an all-in-one SQL script that audits security across a SQL Server. It provides a checklist of items to audit, including the service account, default directories, SA account configuration, passwords, roles and permissions. The script consolidates checks for these items and outputs server-level and database-level audit results in one place, providing a more feasible way to audit security than searching blogs for individual queries. A link is provided to download the script.

Uploaded by

rajiv_ndpt8394
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Server All In One Security Audit Script

[Link]

As a DBA, secure my MS 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 are some critical parts are
also there.

Instead of googling 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.

Check List:

• SQL Server’s service account: All SQL server services must be running under an AD account, if it
is a workgroup server then it should be an Administrator account.
• Default Directories: While installing SQL Server it could ask the default directors for Data, Log
and Backup. We should not keep this in *C:*
• Services Startup: SQL services are able to start when the Windows server is started.
• SA Account: If you don’t want the SA account then go ahead and disabled it. If you need then
rename and make a strong password. For disabling SA you can use the below stored
procedure. exec sp_SetAutoSAPasswordAndDisable
• Password: Don’t use username and password are same, and never leave it as blank.
• Sysadmin User: Don’t give sysadmin role to anyone other than DBAs.
• SQL Port: Its a recommend one to use non default port for SQL server.
• Number of databases: This is not that much affect the SQL servers security, but use less then 100
databases for a medium size server.
• Buildin\Administrator: In SQL server BUILDIN\Administrator login is enabled then go and
immediately disable it. Because who all are have administrator privilege in windows server, those
users can directly access the SQL server.
• Database Level Access: limit the db_owner and db_writter access.

Download the Script: Get it from my Git repo

[Link]
Results:
Server Level Audit

Database level Audit

You might also like