Orphaned users
Issue: SQL Server uses a two-level security model in which logins are defined at the instance level and database users are defined within each database. A database user is typically mapped to a server-level login through a security identifier (SID), and the relationship between the two allows authenticated principals to access database objects according to the permissions granted to the user.
A database user becomes orphaned when no corresponding login exists on the instance for the user's SID. The user still exists in the database with all of its permissions and role memberships intact, but there is no server-level principal that authenticates to it, which means no one can connect as that user.
This finding identifies user databases where one or more users have been determined to not have an associated login at the instance level.
Problem: Orphaned users are most commonly created when databases are restored or attached from another instance. The user records inside the database are preserved, but the corresponding logins from the original instance do not exist on the destination, leaving the users without a working server-level mapping.
The most immediate impact is that legitimate users cannot connect as expected. Applications that rely on a specific user mapping fail to authenticate, and users who should have access find themselves unable to log in. The cause is often confusing because the user clearly exists inside the database with appropriate permissions, but the connection still fails at the authentication step.
Orphaned users also clutter the database security configuration and complicate audit and review. Reviewers must determine for each orphaned user whether it represents a current access requirement that needs to be reconnected, a historical user that should be removed, or a placeholder waiting for a corresponding login to be created. Without active management, orphaned users accumulate over time and obscure the real picture of who has access to the database.
In some scenarios, orphaned users can become a security concern. If a new login is later created on the instance with the same name and is assigned the SID of a previously orphaned user, the new login automatically inherits the orphaned user's permissions and role memberships, potentially granting access that was not intended. This is uncommon but possible, particularly with SQL logins where SIDs can be controlled.
Learn More...Database roles within roles
Issue: SQL Server allows database roles to be nested, meaning one database role can be added as a member of another database role. When this happens, every member of the inner role automatically inherits the permissions granted to the outer role, in addition to any permissions the inner role holds directly.
Role nesting is a legitimate security modeling technique used to compose permissions, but it can also obscure the effective access of users by hiding permissions behind chains of role memberships. The flattened set of permissions a user actually has becomes harder to determine without specifically tracing role membership.
This finding identifies databases where one or more database roles is a member of another database role.
Problem: Nested roles make it harder to answer the basic security question of who has access to what. A user appears to be in a single role, but that role may inherit permissions from another role, which may inherit from yet another role, producing a chain that must be traced before the effective permissions are clear. Reviewers and auditors often miss permissions granted through the chain, leading to incomplete reviews.
The condition also creates risk during permission changes. Granting a permission to one role can unintentionally extend that permission to users in other roles through the nesting relationship, particularly when the team performing the change is unaware of the existing nesting. The effect can be substantial when the receiving role has many members or when the nesting chain is several layers deep.
Nesting also complicates lifecycle changes. When a role is dropped or modified, every role that had it nested must be reviewed to confirm the change does not break expected access. In environments where roles have accumulated over time, the nesting graph can become tangled enough that no one fully understands the implications of any particular change.
Microsoft's general guidance is to keep role structures flat where possible, granting permissions directly to roles that correspond to clear application or user functions rather than building hierarchies through nesting. Flat structures are easier to review, easier to change safely, and easier to document, while nested structures should be used sparingly and with clear purpose.
Learn More...Unusual database permissions
Issue: SQL Server provides several fixed database roles that grant specific subsets of administrative permissions within a database. The db_accessadmin role grants the ability to add and remove users and Windows logins for the database, db_securityadmin grants the ability to manage role memberships and permissions, and db_ddladmin grants the ability to run any data definition language command in the database.
These roles are designed to support delegated administration, where specific responsibilities can be assigned to different principals without granting full db_owner access. They are useful when their scopes are applied carefully, but their effective benefit depends on whether the assigned principals also hold broader roles that already include the same permissions.
This finding identifies user databases where logins or groups have been added to one or more of these roles, where often a principal is also a member of db_owner or holds equivalent broader permissions.
Problem: The db_owner role includes all of the permissions granted by db_accessadmin, db_securityadmin, and db_ddladmin, plus much more. When a principal is a member of db_owner and also of one or more of these specific roles, the additional memberships add no new permissions and only complicate the security model.
Redundant memberships make security review harder. When a user appears in multiple roles, reviewers must work through each role's permissions to understand the effective access, which is more error-prone than reviewing a single role. The redundancy also obscures the actual delegation model, since the team cannot tell whether the additional roles were added for a specific reason or simply accumulated through ad hoc grants.
The condition also indicates that the database security model may not be following least privilege. The fixed database roles db_accessadmin, db_securityadmin, and db_ddladmin exist precisely so that delegated administration can be granted without db_owner, and using them in addition to db_owner defeats the purpose. Reviewing the assignments often reveals opportunities to remove db_owner from principals who only need the more limited delegated authority.
Learn More...db_owner role members
Issue: The db_owner fixed database role is the most powerful role within a single database. Members can perform any configuration and maintenance activity on the database and have effectively unlimited access to its objects, including the ability to create, modify, and drop tables, views, stored procedures, and other objects, manage permissions for other users, and drop the database itself.
This finding identifies user databases where logins or groups have been added to the db_owner role. The condition is not inherently a problem, but it warrants review since db_owner is one of the most consequential role memberships in SQL Server.
Problem: The most significant concern with db_owner is the privilege escalation path it creates when combined with the database owner's identity. Members of db_owner can create objects (procedures, functions, triggers) that execute under the security context of the database owner, which means if the database owner is a privileged login such as sa or another sysadmin, a db_owner member can effectively elevate their own access to sysadmin-equivalent privilege through the owner's identity.
This escalation path is the primary reason the preferred owner check in sp_CheckSecurity exists. When the database is owned by a dedicated, low-permission, disabled login, db_owner members are limited to the database itself, since the owner has nothing to escalate to. When the database is owned by a privileged login, db_owner becomes a path to instance-wide control.
Even without escalation, db_owner is broad enough to warrant careful management. Members can drop tables, alter schemas, change data, modify stored procedures, and grant other users any permission within the database. In production environments, this level of access is rarely appropriate for ongoing operational use and is typically reserved for deployment and maintenance scenarios.
db_owner is also frequently granted more broadly than necessary because it is the simplest way to give someone enough access to do their job. Developers, application support staff, and even individual users sometimes accumulate db_owner memberships over time, when more granular permissions or other fixed roles such as db_datareader and/or db_datawriter would be more appropriate.
Learn More...Failed logins
Issue: SQL Server can log failed login attempts to the SQL Server error log based on the configured login auditing level, which is controlled through the server properties Security page or by editing the relevant registry value. The available levels are none, failed logins only, successful logins only, and both successful and failed logins, with failed-only being the most common production setting.
When a login attempt fails, SQL Server records the reason in the error log along with the source IP address (in supported versions), the login name attempted, and the time of the attempt. These entries are the primary record of authentication failures against the instance.
This finding identifies instances where the error log shows recent failed login attempts.
Problem: Failed login attempts can have many causes, ranging from benign to serious. On the benign end, they may reflect a user who mistyped their password, an application using stale cached credentials after a password change, a misconfigured connection string in a deployment, or a scheduled job pointing at the wrong instance.
On the more serious end, failed logins can indicate brute-force attempts against the instance, particularly when the patterns show many attempts against well-known accounts such as sa, attempts against multiple different login names from the same source, or sustained activity over time. These patterns are characteristic of automated attacks and warrant immediate investigation.
The condition is also worth attention because failed logins often precede successful unauthorized access. An attacker may probe the instance with many failed attempts and eventually succeed, particularly if any login has a weak password (a separate finding) or if account lockout is not configured. Reviewing failed login activity is one of the earliest opportunities to detect and respond to such attempts.
Patterns of failed logins also provide useful signals about the overall health of authentication on the instance. Sustained failures from a known internal source often indicate a configuration problem that is worth fixing, while failures from external or unknown sources warrant a different response. Without regular review, the signal sits in the error log and produces no actionable response.
Learn More...SQL Login Audit does not include failed logins
Issue: SQL Server includes a server-level setting called Login Auditing that controls which authentication events are written to the SQL Server error log. The setting offers four levels: none, failed logins only, successful logins only, and both successful and failed logins. The default setting on installation is failed logins only, which records every authentication failure in the error log along with the source IP address (in supported versions), the login name attempted, and the reason for failure.
This finding identifies instances where the Login Auditing level is set such that failed logins are not being written to the SQL Server error log. The most common cases are instances configured for "none" (no authentication events logged) or "successful logins only" (failures explicitly excluded).
Problem: Failed login attempts are one of the most important signals available for detecting authentication-based attacks against SQL Server. Brute-force attempts, credential stuffing, and reconnaissance activity all show up first as patterns of failed logins, and the error log is the standard, built-in record of these events. Without failed login logging, the team has no immediate visibility into authentication failures unless a separate audit mechanism such as SQL Server Audit has been configured.
The absence of failed login logging also affects troubleshooting of legitimate authentication issues. Users who cannot connect, applications with stale credentials, scheduled jobs pointing at the wrong instance, and similar problems all produce failed logins that the error log would normally capture. Without this record, diagnosing why a connection is failing becomes much harder.
Compliance frameworks generally require some form of authentication event logging, and the SQL Server error log is the simplest and most universal mechanism available. Disabling failed login logging without a compensating control such as SQL Server Audit creates a gap that auditors will typically flag.
The configuration may result from a deliberate change made during troubleshooting (to reduce noise in the error log) that was not reverted, or from a security hardening template that disabled logging without considering the operational impact. In either case, the default setting of "failed logins only" is the right baseline for nearly all production environments and should be restored unless a documented alternative is in place.
Learn More...SQL Server service accounts
Issue: There is no issue. This is simply the name of the account used by the SQL Server service.
Problem: This probably isn't a problem, but did you know this was the account used to run this service? If not, you probably want to note it and any permissions it is assigned in case you need to restore this instance and all it's databases on another server.
Learn More...Database owner is unknown
Issue: Every SQL Server database has an owner SID stored in sys.databases, which links the database to a server-level principal in sys.server_principals. When the SID points to a login that exists on the instance, the owner can be resolved to a name and used by SQL Server for ownership-related operations.
When the owner SID does not match any existing login on the instance, the owner becomes effectively unknown. Queries that try to resolve the owner name return NULL or display the raw SID, and the database is considered to have no valid owner from the server's perspective.
This finding identifies databases whose owner cannot be resolved to a current login. The condition most often appears after a database is restored from another instance where the original owner login does not exist on the destination, or after a login has been dropped without first reassigning the databases it owned.
Problem: A database with an unresolvable owner has no functional trust anchor for ownership-related operations. Cross-database ownership chaining, certain permission resolutions, and operations that need to evaluate the owner's identity can produce errors or NULL results, which can break stored procedures, jobs, and integrations that depend on consistent owner resolution.
The condition also produces audit findings, since reviewers cannot determine who is responsible for the database. The owner column appears blank or shows an unrecognizable SID, which makes it impossible to evaluate whether the ownership is appropriate or whether it represents a privilege escalation risk. This is particularly important because the issues described in the preferred owner finding (members of db_owner creating objects that execute as the owner) still apply even when the owner cannot be resolved by name.
Some SQL Server features behave unpredictably with unresolvable owners. Backup and restore operations may produce warnings, replication and Always On configurations can encounter errors during certain operations, and database mail or other features that resolve principals can fail in ways that are hard to diagnose without checking ownership first.
The condition often persists because it is invisible during normal use. The database functions for read and write operations, applications connect successfully, and routine maintenance runs without obvious problems. The unknown owner only surfaces during specific operations or during security reviews, by which time the original context for who should own the database may have been lost.
Learn More...SQL Server Audit
Issue: SQL Server Audit is the modern auditing framework introduced in SQL Server 2008, used to capture server-level and database-level events for compliance, security monitoring, and forensic purposes. An audit configuration consists of a Server Audit object that defines where the audit data is written (file, Windows application log, or Windows security log) and one or more Server Audit Specifications and Database Audit Specifications that define which events to capture.
This finding identifies instances where one or more SQL Audits are currently active. Active audits are not necessarily a problem, but they warrant review to confirm they are still needed and configured appropriately.
SQL Audits are widely used to satisfy compliance requirements (PCI DSS, HIPAA, SOX) and to support security investigations, but they can also produce significant overhead if configured to capture too much.
Problem: SQL Audits have a meaningful performance and storage cost depending on what they capture. An audit specification that includes high-frequency events such as BATCH_COMPLETED_GROUP or every successful access to common objects can produce enormous volumes of data, consume CPU and I/O on the SQL Server host, and fill disk volumes if directed to file output without proper size management.
Forgotten audit configurations are a common source of operational issues. Audits that were created during a past compliance project, security investigation, or troubleshooting effort sometimes continue running long after their original purpose has ended, accumulating data and overhead with no offsetting benefit. Without periodic review, these audits can persist for years and quietly consume significant resources.
The output destination also matters operationally. File-based audits can fill the configured directory and, depending on how the audit is configured to handle write failures, can either silently lose audit data or shut down the SQL Server service to prevent unaudited activity (which is the default for ON_FAILURE = SHUTDOWN). Either outcome is problematic if not anticipated.
Audit file permissions are another consideration. The directory where audit files are written should be restricted to the SQL Server service account and the security team that reviews the audit data, with no broad access. Audit files contain sensitive event data and should be protected at rest with the same care as any other sensitive log.
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: