Backup compression disabled
Issue: SQL Server supports an option called WITH COMPRESSION on backup commands, which compresses the backup stream as it is written. The result is a smaller backup file, faster backup duration in many cases (since less data is written to disk), and faster restore times for the same reason.
The server level configuration option backup compression default controls whether compression is applied automatically to every backup that does not explicitly specify a compression option. The default value is 0, meaning compression is not used unless explicitly requested in each backup command.
This finding identifies instances where backup compression default is set to 0, leaving compression off by default for any backup that does not specifically opt in.
Problem: Without compression, backup files are significantly larger than they need to be, often two to four times the size of the equivalent compressed backup depending on the data. This consumes more storage on the backup target, which is multiplied across every full, differential, and log backup retained for the recovery window.
Larger backups also take longer to transfer over the network when copied to offsite storage, secondary backup targets, or disaster recovery locations. The increased transfer time affects backup windows, recovery point objectives for offsite copies, and the cost of storage and bandwidth.
Restore times are similarly affected. A larger backup file takes longer to read from disk during a restore, which extends recovery time during exactly the moments when speed matters most. Compressed backups typically restore faster than uncompressed ones because the disk read time saved by smaller file size outweighs the small CPU cost of decompression.
Learn More...Backup checksum disabled
Issue: SQL Server supports an option called WITH CHECKSUM on backup and restore commands, which causes SQL Server to verify page checksums as the database is read during a backup, calculate a checksum across the backup stream itself, and verify all of these checksums during a restore or RESTORE VERIFYONLY operation. This protects against corruption introduced during backup, transit, or storage of the backup file.
The server level configuration option backup checksum default controls whether WITH CHECKSUM is applied automatically to every backup and restore that does not explicitly specify a checksum option. The default value is 0, meaning checksums are not used unless explicitly requested in each backup command.
This finding identifies instances where backup checksum default is set to 0, leaving the protection off by default for any backup that does not specifically include it.
Problem: Without backup checksums, corruption that occurs during the backup process or while the backup file is at rest can go undetected. SQL Server reads pages from the database, writes them to the backup, and stores the backup, all without verifying that the data on the way through is consistent with what was originally written. Corruption introduced by faulty memory, storage, network paths, or backup software can quietly produce a backup that cannot be restored successfully when needed.
The WITH CHECKSUM option also catches corruption that already exists in the source database, since SQL Server verifies page checksums as it reads. This provides an additional layer of corruption detection that complements DBCC CHECKDB, often surfacing issues earlier and during routine activity rather than only during scheduled integrity checks.
The default off behavior means that organizations must remember to specify WITH CHECKSUM in every backup script, maintenance plan, and third-party backup tool configuration. In practice, some backups are taken with the option and others are not, producing an inconsistent protection profile that depends on which mechanism took the backup. Administrators reviewing backup history may not realize that some backups offer the protection while others do not.
Setting the default to enabled removes the dependency on every individual script and tool remembering to include the option. Every backup, including those taken by ad hoc commands, vendor software, and maintenance plans, is automatically protected, and the protection is consistent across the environment.
Learn More...Ole Automation Procedures
Issue: OLE Automation Procedures are a set of system stored procedures that allow T-SQL code to instantiate and interact with OLE Automation objects. These procedures provide a way for SQL Server to call out to COM objects on the host operating system, manipulate them, and use their methods and properties from within database code.
The functionality is controlled by the Ole Automation Procedures server configuration option, which is disabled by default. When enabled, any login with permission to execute the OLE Automation procedures (typically members of the sysadmin role by default) can use them.
This finding identifies instances where OLE Automation Procedures are currently enabled.
Problem: OLE Automation Procedures expand the attack surface of SQL Server beyond traditional T-SQL operations, allowing managed code paths to interact with operating system COM objects. The objects accessible through this mechanism can perform a wide range of activities including file system operations, network calls, registry manipulation, and execution of external commands, all running under the SQL Server service account context.
The feature is a known SQL injection amplifier in the same way that xp_cmdshell and Ad Hoc Distributed Queries are. An injection vulnerability in an application connecting with sufficient privileges becomes significantly more dangerous when OLE Automation Procedures are enabled, since the attacker gains a path to interact with operating system functionality through COM objects rather than only through database operations.
Modern alternatives exist for nearly every legitimate use case that historically required OLE Automation Procedures. PowerShell job steps in SQL Server Agent, SSIS packages, CLR integration with strict security, and external orchestration tools all provide better, more auditable, and more maintainable approaches to interacting with operating system functionality. The presence of OLE Automation Procedures in an environment usually indicates older code patterns that could be modernized.
The condition often persists because OLE Automation Procedures were enabled years ago for a specific use case, and the original requirement may have been retired or replaced without anyone disabling the feature. Forgotten enablement leaves the attack surface available with no offsetting benefit.
Learn More...Database owner is not preferred owner
Issue: This check, run by sp_CheckSecurity using the @PrefferredDBOwner parameter, identifies databases whose owner does not match the preferred owner specified for the environment. When no preferred owner is provided to the procedure, sa is treated as the default preferred owner, although using sa for this purpose is not a recommended security practice.
Database ownership is recorded at the server level in sys.databases and is updated through ALTER AUTHORIZATION ON DATABASE. The owner determines the security context of the dbo user inside the database and is the trust anchor for ownership-chained operations that cross out of the database.
This finding flags any database whose current owner does not match the preferred value, indicating that ownership has drifted from the standard or was never set correctly during database creation, restore, or attach.
Problem: Members of the db_owner fixed database role can create objects in the database that execute under the security context of the database owner. If the database owner is a member of a role with elevated server-level permissions, such as sysadmin, this becomes a direct privilege escalation path: anyone in db_owner can create code that runs with the owner's elevated privileges, effectively inheriting access far beyond the database itself.
The escalation path is particularly dangerous because it does not require any change to server-level role membership. The db_owner member writes code, the code executes as the database owner, and the database owner's elevated permissions apply. This pattern is commonly cited in SQL Server security guidance and is one of the main reasons that database ownership deserves the same scrutiny as server-level role membership.
Inconsistent ownership across databases also complicates audit and review. When ownership is set ad hoc, often to whoever happened to create or restore the database, the security model becomes unpredictable and difficult to reason about. Reviews must then evaluate the privilege level of each owner separately, multiplying the audit work.
The condition often persists silently because database ownership rarely surfaces in normal monitoring, and the escalation paths it enables are only exploited in scenarios that involve a privileged owner combined with broad db_owner membership. Without specific checks, ownership drifts further from the standard over time as databases are moved, restored, and refreshed.
Learn More...Number of SQL Server error log files
Issue: SQL Server maintains a series of error log files that record service startup messages, login activity, errors, backup history, and many other diagnostic events. The current log is named ERRORLOG, with archived copies named ERRORLOG.1, ERRORLOG.2, and so on. A new log file is created each time the SQL Server service restarts or when the log is manually cycled.
By default, SQL Server retains only six archived error logs in addition to the current one, for a total of seven. Once that limit is reached, each new log creation causes the oldest archive to be deleted permanently. This finding indicates there are less than 12 error log files.
Problem: The default of six archived logs is too low for most production environments. On instances that restart frequently (due to patching, failovers, or maintenance) or that cycle the log on a schedule, six archives can cover only a few weeks or even days of history.
When older logs are deleted, the diagnostic record of past events is lost. This makes it impossible to investigate issues that surfaced earlier, correlate failures with patterns over time, or provide historical evidence during audits and incident reviews.
The error log is often the first place administrators look when troubleshooting, and missing entries from previous weeks or months can turn a routine investigation into guesswork. The default also predates modern monitoring practices and reflects an era when log files were considered short term operational data rather than a long-term diagnostic record.
Learn More...Database Mail XPs Enabled
Issue: Database Mail is the SQL Server feature for sending email from within the database engine, used for SQL Server Agent job notifications, alerts, and application-driven email messages. It depends on a set of extended stored procedures controlled by the Database Mail XPs server configuration option, which must be enabled before Database Mail can be used.
The setting is off by default and is enabled either explicitly to support a known mail use case or as a side effect of installing features such as SQL Server Agent alerts that depend on Database Mail. Once enabled, any login with the appropriate permissions can call procedures such as sp_send_dbmail to send messages through the configured mail profiles.
This finding identifies instances where Database Mail XPs is set to 1. It is not necessarily a problem, but it is worth noting in case the team is unaware that mail capability is active or unaware of the considerations that come with allowing email to be sent from the engine.
Problem: Database Mail is a useful and widely deployed feature, and most instances that have it enabled are using it for legitimate operational purposes such as job failure notifications and severity-based alerts. The condition is worth surfacing primarily so the team can confirm that the use case is intentional and that permissions are appropriately scoped.
The main risk with Database Mail relates to who can call the mail procedures. By default, members of the DatabaseMailUserRole in msdb can send email through configured mail profiles, and this membership is sometimes granted more broadly than necessary. A login with this access can send email to arbitrary recipients with arbitrary content, which creates several abuse paths.
The most operationally serious risk is using a SQL Server instance to participate in distributed denial-of-service or spam campaigns. A compromised application account or a malicious insider with mail permissions could send large volumes of email to external addresses, both consuming resources on the SQL Server host and potentially causing the instance's source domain or IP address to be added to email blocklists. Recovery from blocklisting is difficult and can affect legitimate mail from the same domain.
Database Mail can also be used as an exfiltration channel. An attacker who reaches the database with mail permissions can send query results or other sensitive data to external email addresses, bypassing network-based data loss prevention controls that focus on traditional egress paths. The activity may not appear unusual to monitoring tools that do not specifically watch for outbound mail patterns.
Learn More...Ad Hoc Distributed Queries
Issue: The Ad Hoc Distributed Queries server configuration option controls whether SQL Server allows the use of OPENROWSET and OPENDATASOURCE functions to access external data sources without first defining a linked server. When enabled, queries can connect to other databases, files, and OLE DB providers on the fly using credentials and connection strings supplied directly in the query.
This finding identifies instances where Ad Hoc Distributed Queries is set to 1. The setting is off by default and is enabled either intentionally to support specific data integration patterns or as a side effect of installation scripts and vendor applications that use these functions.
The feature provides flexibility for one off data access scenarios, but it also expands the attack surface of the instance in ways that warrant review.
Problem: Ad hoc distributed queries bypass the normal linked server configuration, which means there is no central place to review or audit which external sources can be accessed. Any user with the necessary permissions can construct a connection string and reach external data sources directly, including network shares, other database servers, and any OLE DB accessible target.
The feature is a known SQL injection amplifier. When an injection vulnerability exists in an application connecting with sufficient privileges, ad hoc distributed queries give the attacker a path to reach external systems, exfiltrate data to attacker controlled endpoints, or read local files through providers such as the file system OLE DB provider.
Connection strings used with OPENROWSET and OPENDATASOURCE often contain embedded credentials, which means passwords appear in plain text in query text, plan cache entries, and any logs that capture queries. This produces credential leakage that is hard to detect and remediate, particularly when the same credentials are reused across multiple queries or applications.
Linked servers, while not without their own security considerations, provide a defined configuration with explicit security mappings, auditable definitions, and centralized management. Ad hoc distributed queries provide essentially the same external access without any of these controls, which is why disabling the feature is the recommended default.
Learn More...IP Address
Issue: SQL Server instances bind to one or more IP addresses on their host, configured through SQL Server Configuration Manager under the IP Addresses tab of the TCP/IP protocol properties. The IP addresses determine which network interfaces the instance listens on and where clients can connect. Each address can be configured separately for which port to use and whether the address is enabled.
This finding reports the IP address or addresses currently in use by the SQL Server instance. It is not an issue in itself, but the address is worth recording and reviewing because several operational and security considerations depend on it.
Problem: The most important security concern is whether the instance is reachable from untrusted networks. A SQL Server instance with a publicly accessible IP address is exposed to the entire internet, which means it is constantly subject to port scans, brute-force authentication attempts, and exploitation attempts targeting any known vulnerability. Public exposure of SQL Server is rarely appropriate and is one of the most common contributors to compromise.
Even on private networks, the IP address determines which segments of the network can reach the instance. Network segmentation and firewall rules should restrict inbound access to only the specific source addresses that legitimately need to connect, rather than allowing any host on the network to attempt connections. The configured IP addresses are the starting point for understanding the actual exposure.
Disaster recovery planning also depends on knowing the IP addresses involved. Connection strings, application configuration, DNS records, and firewall rules all reference specific addresses, and recovering an instance to a new host typically requires updating these references. Documentation that captures the addresses in use, the DNS names that resolve to them, and the dependencies that reference them is essential for reliable recovery.
Learn More...Explicit permissions granted to the public role
Issue: The public server role is a special fixed server role that includes every login on the SQL Server instance automatically. Every login is a member of public by default, and membership cannot be removed, which means any permission granted to public is effectively granted to every authenticated principal on the instance.
By default, public holds a small, carefully scoped set of permissions that allow basic operations such as connecting to the instance and viewing certain server-level metadata. These default permissions are documented and accepted as the baseline expectation for any login that can authenticate.
This finding identifies instances where additional permissions have been granted explicitly to the public role beyond the default set.
Problem: Permissions granted to public apply to every login on the instance, which means any access granted to the role is universally available. A grant intended to support one specific use case ends up extending the same access to every other login, including service accounts, vendor-managed accounts, individual users, and any logins added in the future without explicit review.
This produces unexpected access paths that are difficult to track. Reviewers examining individual login permissions may not realize that significant access is also granted through public, since the role is rarely the focus of permission reviews. A user appears to have minimal explicit permissions, while in fact they have access to whatever has been granted to public.
Grants to public also undermine the principle of least privilege. The role exists primarily to hold the small set of permissions that every authenticated user genuinely needs, and adding to it is rarely the right answer to a permission requirement. The correct approach is almost always to grant the necessary permissions to a specific role or principal that represents the actual users who need the access.
Moreover, there are some permissions like BACKUP DATABASE that can allow anyone to make a copy of your database, which could have serious consequences in the event of a data breach.
Learn More...Database owner is different from owner in master
Issue: Every SQL Server database has an owner, recorded in two related but distinct places. The master database tracks the owning login through the owner_sid column in sys.databases, which links to a server-level principal in sys.server_principals. Inside the database itself, the dbo user maps to that owner login through the sid column in sys.database_principals.
These two values should match, since the dbo user inside the database is meant to represent the same principal that owns the database at the server level. They can become out of sync as a result of restoring a database from another instance, attaching a database file from a different environment, or manual changes that updated one location without updating the other.
This finding identifies databases where the SID recorded in master for the database owner does not match the SID stored for the dbo user inside the database itself.
Problem: The condition often indicates that the database has been moved between instances without proper ownership reassignment. After a restore or attach, the dbo user inside the database retains the SID from the original instance, while the master database records the owner SID of whoever performed the restore or attach. Over time these mismatches accumulate, particularly in environments where databases are routinely refreshed from production to non-production for testing.
The mismatch produces inconsistent and sometimes surprising behavior in operations that depend on database ownership. Permissions checks, certain ownership-chained queries, and operations that resolve dbo to a server-level identity can produce different results depending on which view of the owner SQL Server consults, leading to errors or unexpected access outcomes.
Mismatched ownership also complicates audit and compliance review. The login shown as the database owner in administrative tools may not have the authority that is actually granted by the dbo mapping inside the database, and vice versa. Reviewers cannot rely on the owner field alone to understand who has elevated access to the database.
In some cases, the original owning login no longer exists on the current instance. The database still functions, since dbo-mapped operations resolve through the internal SID, but operations that try to resolve the owner through master can fail or produce NULL results. This is also a common source of broken ownership chaining for cross-database operations.
Learn More...About sp_checks
This page contains a list of SQL Server configuration checks performed by Straight Path's suite of sp_check tools. For more details about our free tools, select one from the following list: