sp_check: SQL Server Database Checks

0 - Information Only

SQL Agent jobs owned by users

Issue: Every SQL Server Agent job has an owner, which is the login responsible for the job and used to determine the security context in which certain job steps execute. The owner of a job affects how the job is treated during execution, who can modify it, and what happens when the owning login changes or is removed.

This finding identifies SQL Server Agent jobs whose owners are individual user logins rather than a system or service principal. The condition is most often the result of jobs being created ad hoc by administrators or developers without consideration of long-term ownership.

Problem: When a job is owned by an individual user login, the job becomes tied to that person's account lifecycle. If the user leaves the organization, changes roles, or has their login disabled or dropped, the job can fail to start, fail to send notifications, or stop running entirely depending on the specific configuration and SQL Server version.

Job ownership also affects security context. Job steps that run as the owner inherit that user's permissions, which means a job owned by a sysadmin runs with full instance privileges even if the work being performed does not require them. This creates a hidden privilege escalation path, since anyone who can modify the job can effectively execute code as the owner.

User owned jobs also complicate audit and review. The presence of jobs owned by former employees, by personal accounts, or by accounts with elevated privileges produces audit findings and indicates that job ownership is not being managed deliberately. The audit trail of job changes also becomes harder to interpret when ownership reflects whoever happened to create the job rather than a consistent ownership model.

The condition often persists because job ownership is rarely visible in normal monitoring. Jobs continue to run successfully under their original owner long after the owning user has moved on, and the issue surfaces only during audits, security reviews, or when the owning account is finally removed and jobs begin to fail.

Learn More...

Cross-database ownership chaining

Issue: Ownership chaining is a SQL Server security mechanism that allows a user to access objects through a stored procedure or view without needing direct permissions on the underlying objects, as long as the calling object and the underlying objects share the same owner. Within a single database, ownership chaining is enabled by default and is the basis for the common pattern of granting users execute permission on a stored procedure while denying direct access to the tables it reads or writes.

Cross database ownership chaining extends this mechanism across database boundaries. When enabled, ownership chains can span databases as long as the relevant objects are owned by the same login at the server level. The behavior is controlled by two settings: the server level cross db ownership chaining configuration option, which enables the feature globally, and the database level DB_CHAINING option, which enables it on a per database basis.

This finding identifies instances where cross database ownership chaining is enabled, either at the server level or for one or more individual databases.

Problem: Cross database ownership chaining bypasses the permission checks that normally occur when a query crosses database boundaries. A user with execute permission on a stored procedure in one database can read or write objects in another database with no direct permissions there, as long as the ownership chain is unbroken.

This expands effective access in ways that are not obvious to administrators, since the chained access does not appear in any user's direct permission grants. Tracing the full set of accessible objects becomes difficult when chained procedures call other chained procedures across multiple databases.

The feature also creates privilege escalation paths that are hard to anticipate. A user with db_owner rights in one database can create dbo owned objects that reach into other databases owned by the same login at the server level, which is often a privileged account such as sa.

Microsoft recommends keeping cross database ownership chaining disabled at the server level and enabling it only on specific databases where it is genuinely required, with documentation of the reason and the access paths involved.

Learn More...

xp_cmdshell enabled

Issue: xp_cmdshell is an extended stored procedure in SQL Server that executes a command string as an operating system shell command and returns the output as rows of text. It runs the command under the context of the SQL Server service account by default, or under a proxy account if one has been configured. The feature is disabled by default starting with SQL Server 2005 and is controlled by the xp_cmdshell server configuration option.

This finding identifies instances where xp_cmdshell is enabled. The setting is sometimes turned on intentionally to support administrative scripts, scheduled jobs that need to interact with the file system, or vendor applications that depend on shell access, and other times it is enabled by an administrator who simply wanted to run a single command and never disabled it afterward.

Problem: xp_cmdshell is widely discussed as a security concern, but the actual risk picture is more nuanced than the configuration setting alone suggests. By default, only members of the sysadmin fixed server role can execute xp_cmdshell, and members of sysadmin already have the ability to enable or disable the feature at will using sp_configure. This means that for an attacker who has already obtained sysadmin level access, the on or off state of xp_cmdshell is essentially a formality, since they can flip the setting and run commands at any time.

For this reason, the more important security control is who has membership in the sysadmin role (and the equivalent CONTROL SERVER permission and securityadmin role), not whether xp_cmdshell is currently enabled. An instance with xp_cmdshell disabled but with excessive sysadmin membership is no more secure against a compromised privileged account than an instance with the feature enabled, since the elevated account can change the setting in seconds.

That said, xp_cmdshell does increase risk in specific scenarios that warrant attention. When the xp_cmdshell proxy account is configured, non sysadmin users can be granted execute permission on xp_cmdshell and run commands under the proxy account context. This delegated access path is sometimes used to allow application accounts or scheduled jobs to run shell commands, and the proxy account's permissions on the host become a critical security boundary. A poorly scoped proxy account, or one with excessive permissions, can become a privilege escalation path that does not require sysadmin to exploit.

Enabling xp_cmdshell also expands the impact of certain attack patterns, including SQL injection. An injection vulnerability in an application connecting with a privileged account becomes significantly more dangerous when xp_cmdshell is available, since the attacker can move from data exfiltration to operating system command execution in a single step. Disabling xp_cmdshell does not prevent the injection vulnerability itself, but it does limit the immediate options available to the attacker if they reach the database.

Learn More...

CLR enabled

Issue: The clr enabled server configuration option controls whether SQL Server allows the execution of managed code through the Common Language Runtime (CLR) integration feature. When enabled, developers can create stored procedures, triggers, user defined functions, and aggregates written in .NET languages such as C# and have them execute inside the SQL Server process.

This finding identifies instances where clr enabled is set to 1. The setting is off by default and is enabled either explicitly to support CLR based code or as a side effect of installing certain features and vendor applications that depend on CLR functionality.

Problem: CLR integration extends the attack surface of SQL Server beyond traditional T-SQL, allowing managed code to perform operations that T-SQL alone cannot, including file system access, network calls, and direct interaction with operating system resources. While CLR assemblies are sandboxed by default, the protections depend on how the assembly is registered and on the overall security model of the instance.

Historically, CLR assemblies could be registered with one of three permission sets: SAFE, EXTERNAL_ACCESS, or UNSAFE. SAFE was the default and provided the strongest containment, while EXTERNAL_ACCESS and UNSAFE allowed progressively broader access to system resources.

Microsoft introduced a security advisory and a new configuration option, clr strict security, in SQL Server 2017 and later. With strict security enabled (which is the default in SQL Server 2017 and later), all assemblies are treated as UNSAFE regardless of their declared permission set, and the engine requires either that the assembly be signed with a certificate or asymmetric key that has been granted UNSAFE ASSEMBLY permission, or that it be added to the trusted assemblies list using sp_add_trusted_assembly. This change closes the older privilege escalation paths but requires that assemblies be properly signed or trusted, which not all environments have done.

When clr enabled is on without clr strict security enforced, or when assemblies are run as UNSAFE without proper signing, the instance is exposed to risks including code that can read or modify files on the SQL Server host, make outbound network connections to exfiltrate data, execute arbitrary operating system commands under the SQL Server service account context, and bypass SQL Server permission checks through the elevated execution context.

Learn More...

Password vulnerabilities

Issue: SQL Server logins (those using SQL authentication rather than Windows authentication) are protected by a password stored as a hash in the master database.

This finding flags SQL logins whose passwords have been verified as trivially weak, including passwords that are blank, the same as the login name, or the literal word "password". These are among the first values an attacker tries during a brute force or credential stuffing attack.

The condition is most often found on logins created during initial setup, on logins associated with vendor applications that ship with default credentials, or on developer and test logins that were created with placeholder passwords and never updated.

Problem: Weak passwords on SQL logins are one of the most direct paths to a SQL Server compromise. Any attacker who can reach the instance over the network and knows or guesses the login name can authenticate immediately, with no exploit required. Tools that perform automated brute force attacks against SQL Server start with exactly the kinds of values flagged here, so an instance with even one weak login is effectively unauthenticated for that account.

Once authenticated, the attacker has whatever permissions the login holds. If the weak login is a member of sysadmin, has CONTROL SERVER, or has been granted broad permissions for application convenience, the result is full instance compromise, including the ability to read and exfiltrate data, modify or destroy databases, and pivot to other systems through linked servers and the operating system.

Even when the weak login has limited permissions, it provides a foothold for further attack. SQL Server has many documented privilege escalation paths, particularly when combined with other configuration weaknesses such as weak service account permissions or older patch levels, so any authenticated access can become a stepping stone to broader compromise.

Weak passwords are also a serious compliance issue. Most regulatory frameworks (PCI DSS, HIPAA, SOX, ISO 27001) require enforced password complexity, minimum lengths, and regular rotation, and a weak SQL login violates these requirements directly.

Learn More...

Invalid Windows login

Issue: SQL Server supports Windows authentication, allowing logins to be created from Active Directory user accounts, Active Directory groups, and local Windows accounts. When a login is created, SQL Server stores the security identifier (SID) of the underlying Windows or Active Directory principal, which is the durable reference used to validate the account at connection time.

The system stored procedure sp_validatelogins checks every Windows login on the instance and returns a list of any whose underlying Windows accounts no longer exist. This finding flags instances where sp_validatelogins has returned one or more rows, indicating that SQL Server logins exist for Windows users or groups that have since been deleted from Active Directory or the local account database.

These invalid logins remain in sys.server_principals and may still appear in role memberships and permission grants, even though the underlying account is gone and the login can no longer authenticate.

Problem: Invalid Windows logins clutter the SQL Server security configuration and make it harder to maintain an accurate picture of who has access to the instance. During audits and security reviews, every login must be evaluated and justified, and invalid accounts add noise that makes the legitimate entries harder to review.

These logins also retain whatever permissions and role memberships they had when the underlying Windows account was deleted. The permissions are not enforceable while the login is invalid (since no one can authenticate as the deleted account), but they remain attached to the login record. This complicates security reporting and makes it appear that more principals have access than is actually the case.

For Active Directory groups specifically, an invalid group login indicates that the group was actually deleted, since renaming a group preserves its SID. Any access that was previously granted through that group is gone, but the login still appears in permission grants and role memberships, which can mislead future reviews of who has access to what.

Learn More...

CONTROL SERVER permissions

Issue: CONTROL SERVER is the highest level permission in the SQL Server permission hierarchy, granting its holder full control over the entire instance. The permission was introduced as part of the granular permissions model in SQL Server 2005 and is functionally equivalent to membership in the sysadmin fixed server role, with only minor edge case differences in how certain internal operations are evaluated.

This finding identifies logins that have been granted CONTROL SERVER permission directly, separate from the explicit list of sysadmin role members. The intent of the review is to confirm that only approved accounts have this level of access and to remove any that do not require it.

Problem: A login with CONTROL SERVER permission has the same effective access as a sysadmin and can perform any action on the instance. They can read, modify, or drop any object in any database, take and restore backups (a path to data exfiltration), change the permissions of every other principal, alter audit configuration, enable features such as xp_cmdshell to execute operating system commands, create or modify SQL Server Agent jobs, and use linked servers to reach other instances.

Despite this equivalence, CONTROL SERVER grants are sometimes treated as a less risky alternative to sysadmin membership, which is a misconception. The two paths produce essentially the same outcome and deserve the same level of scrutiny in audits and reviews.

CONTROL SERVER grants can also be more difficult to spot than sysadmin role members. Sysadmin membership is visible in a single well-known query, while CONTROL SERVER permissions are recorded in the broader server permissions catalog and are easy to miss during a casual security review. This makes them an attractive path for attackers or malicious insiders who want to maintain elevated access without appearing in the standard list of sysadmins, and they can persist undetected longer than direct sysadmin membership.

Learn More...

securityadmin role members

Issue: The securityadmin fixed server role in SQL Server grants its members the ability to manage logins and their properties at the instance level. Members can create, alter, and drop logins, reset passwords for SQL logins, and grant, deny, or revoke server level permissions to other principals. The role exists to allow security administration to be delegated without granting the full power of the sysadmin role.

This finding identifies the logins currently included in the securityadmin role on a SQL Server instance. The intent of the review is to confirm that only approved accounts have this level of access and to remove any that do not require it.

Problem: Despite being framed as a delegated security administration role, securityadmin is effectively equivalent to sysadmin in terms of the access it can grant to itself or others. A member of securityadmin can create a new login and grant that login the CONTROL SERVER permission, which is functionally equivalent to sysadmin and bypasses essentially all SQL Server permission checks.

A securityadmin member can also reset the password of any SQL login, including logins that already have sysadmin or other elevated privileges, then connect as that login and inherit its full permissions. This means anyone in securityadmin can promote themselves to full instance control through a path that does not require any cooperation or approval from existing sysadmins.

The implications are the same as if the account were a member of sysadmin directly: they can read, modify, or drop any object in any database, take and restore backups (a path to data exfiltration), change audit configuration, enable features such as xp_cmdshell, and use linked servers to reach other instances. The only difference is that the path to that level of control is one extra step, which provides essentially no real security benefit.

Learn More...

sysadmin role members

Issue: The sysadmin fixed server role in SQL Server grants its members complete control over the entire SQL Server instance and every database it hosts. Membership is granted by adding logins (Windows accounts, Active Directory groups, or SQL logins) to the role, either explicitly through sp_addsrvrolemember or ALTER SERVER ROLE, or implicitly through other paths such as the local Windows Administrators group on older configurations and the SQL Server service account itself.

This finding identifies the logins currently included in the sysadmin role on a SQL Server instance. The intent of the review is to confirm that only approved accounts have this level of access and to remove any that do not require it.

Problem: Membership in sysadmin is the most powerful permission inside SQL Server and bypasses all permission checks within the database engine. Any account in this role can read, modify, or drop any object in any database on the instance, regardless of the explicit permissions assigned to those objects.

A sysadmin can take and restore backups, including restoring databases to other servers or external locations, which is effectively a path to data exfiltration. They can change the permissions of every other login and user on the instance, create and modify SQL Server Agent jobs that run arbitrary code under the service account context, and enable features such as xp_cmdshell to execute operating system commands. They can also alter audit configuration, drop traces and Extended Events sessions, and modify or remove evidence of their own activity.

Sysadmin level access also extends beyond the database engine in subtle ways. A sysadmin can change the SQL Server service account configuration through certain features, install or modify CLR assemblies, and use linked servers to reach other SQL Server instances with the credentials configured on those links. In environments with multiple instances or trust relationships between servers, a single compromised sysadmin account can become a pivot point for broader compromise.

Excessive membership in sysadmin also undermines audit and compliance posture. Regulatory frameworks such as PCI DSS, HIPAA, SOX, and ISO 27001 require that administrative access be granted on a least privilege basis and that membership in privileged roles be reviewed and justified. A sysadmin role with many members, with stale entries from former employees, or with broad Active Directory groups granted membership creates audit findings and increases the blast radius of any compromised credential.

Learn More...

Local Administrators group

Issue: The local Administrators group on a Windows server grants its members full control over the operating system and everything running on it, including any SQL Server instance installed on the host.

By default, SQL Server grants the local Administrators group sysadmin level access to the database engine through the BUILTIN\Administrators login on older versions, and even when that login is removed, members of local Administrators retain the ability to assign themselves SQL Server permissions through several documented paths.

This finding identifies users and groups currently included in the local Administrators role on a server that hosts a SQL Server instance. The intent of the review is to confirm that only approved accounts have this level of access and to remove any that do not require it.

Problem: Membership in the local Administrators group is the most powerful permission on a Windows server. Any account in this group can install software, change system configuration, modify or delete files (including SQL Server data and backup files), read or alter the registry, and execute arbitrary code at the operating system level.

The implications for SQL Server are equally severe. A local administrator can start the SQL Server service in single user mode, which automatically grants any connecting account sysadmin privileges, providing a path to full database control even on instances configured with strict SQL Server level security. They can also read SQL Server data files directly from disk, access backups, modify service account credentials, and install drivers or hooks that intercept SQL Server activity.

Once an account has sysadmin privileges in SQL Server, the consequences are equally broad. A sysadmin can read, modify, or drop any object in any database, take and restore backups (including to external locations), alter the permissions of every other user on the instance, enable features such as xp_cmdshell to execute operating system commands, and create or modify SQL Server Agent jobs to run code under the service account context. Excessive membership in the local Administrators group also undermines audit and compliance posture. Most regulatory frameworks require that administrative access be granted on a least privilege basis and that membership in privileged groups be reviewed and justified. A local Administrators group with many members or with stale entries from former employees or retired projects creates audit findings and increases the blast radius of any compromised credential.

Learn More...
Previous 1…11121314 Next

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: