Evaluating Security Vulnerabilities in Your SQL Server Database Environment
Do you know who has the keys to your SQL Server database engine? This article examines the critical importance of mastering SQL Server’s dual-layered security model to provide a strategic roadmap for IT Managers and DBAs to audit and harden their database environments against evolving internal vulnerabilities.
Tackling permission creep, orphan users and ghost accounts
One of the core security concepts for SQL Server – as for any IT system – are its users, logins and their permissions. Specifically, we are going to look at how changes over time can introduce security holes for your database servers.
Before we dig deep into common issues, it must be observed that SQL permissions exist at two levels: both the server (instance) and the database. Each of these tiers must be considered. Database “users” are mapped to server level “logins” and a login may have multiple database users; one per database.
Server-level permissions
Server-level permissions control access to the SQL Server instance as a whole. A SQL Authentication account is a simple login and password unconnected to any Windows level security. Windows authentication requires a principal either in Active Directory on an Entra ID and is considered more secure than SQL Authentication as a result.
Principals: SQL logins, Active Directory principals, Entra IDs, server level roles
Key roles: fixed roles like sysadmin, serveradmin, securityadmin grant permissions to change the security profile of the server
Database-level permissions
Database-level permissions apply within a specific database and are assigned to “users.”
Principals: users, database roles (fixed roles like db_owner and user-defined roles)
Key roles: db_owner has full control within the database
The hidden danger of permission creep
Permission creep is a common and often silent threat in many SQL Server environments. It occurs when a user or application gradually accumulates more permissions than they actually need to perform their job, which can result in inflated responsibility and unnecessary risk. This usually happens for a few key reasons:
Temporary access grants: A developer requests elevated permissions for a one-time task or urgent fix, and those permissions are never revoked.
Role proliferation: Users are added to multiple roles over time, accumulating the union of all those roles’ permissions.
Database owner (dbo) misuse: Granting db_owner (or similar high-privilege roles) because it’s the “quickest fix” to a permission error.
Sysadmin for application logins: Many applications request sysadmin as a quick way to set up the application login. Reading deeper into the application documentation will often show a much smaller subset of required permissions.
The three core risks of permission creep
Left unchecked, permission creep introduces three major risks to your SQL Server instance:
Compliance failures: Many regulatory frameworks, such as HIPAA, GDPR, and PCI DSS, mandate the principle of Least Privilege. This principle states that users should only have the minimum permissions necessary to perform their job functions.
Permission creep directly violates the principle of Least Privilege. During a compliance audit, excessive, unlogged, or unneeded permissions can result in:
Fines and penalties: Failure to demonstrate strict access control is a common source of non-compliance.
Reputational damage: A public security incident resulting from poor access control can severely damage trust.
Operational complexity: Overly complex permission structures are difficult to maintain and troubleshoot. When a user reports an access issue, the DBA must wade through layers of role memberships and explicit permissions, many of which are redundant.
This complexity can lead to:
Slower incident response: Diagnosing the root cause of an issue takes longer when permissions are not clean.
Accidental revocations: Trying to clean up redundant permissions can inadvertently break a necessary function because it was relying on an obscure, unnecessary permission granted years ago.
Orphaned users
An orphaned user occurs when a database user in a specific SQL Server database is mapped to a server-level login that no longer exists on the SQL Server instance.
| Cause | Effect | Risk |
|---|---|---|
| Deleted Server Login | The User still exists in the database. | If a new server login is created with the same name, it might accidentally gain access to the database with the orphaned user’s existing permissions. |
| Database Migration | Moving a database to a new server without moving the logins. | The user loses access, leading to application downtime and manual rework. |
To circumvent these risks, it’s important to ensure that users are correctly mapped to logins and that when logins are deleted, all their mapped database users are also purged.
Ghost accounts
The term “ghost account” often refers to security principals (logins or users) that should no longer exist, but still do, often due to poor housekeeping. These are often logins that belong to former employees, deactivated application services, or temporary maintenance accounts that were never properly cleaned up.
Risks associated with ghost accounts:
Security vulnerability: If a malicious actor compromises an external system, they could potentially exploit this abandoned login.
Audit failure: These accounts make security audits more complex and can lead to non-compliance with regulations that require “least privilege” and timely de-provisioning.
Permission creep: As described earlier permission creep describes the gradual accumulation of unnecessary security permissions over time.
Best practices for managing permissions
A good understanding of the SQL Server login hierarchy allows for the implementation of robust security policies:
Principle of least privilege: Only grant the minimum permissions necessary for a user or application to perform its required functions. Avoid using sysadmin or db_owner level roles without clear justification.
Use roles extensively: Granting permissions to roles (both server and database) rather than individual logins/users dramatically simplifies management and auditing.
Leverage schemas for separation: Use schemas to logically separate objects (e.g., HR_Schema, Finance_Schema) and control access at the schema level.
Use Windows/EntraID authentication: Whenever possible, use Windows logins over SQL Server logins to centralise authentication management.
Documentation: Record all permissions granted, why they are required and when they were granted.
Auditing: Regularly review all users, logins and permissions/roles granted cross referencing with your current Active Directory (AD) and HR records. Enable SQL failed login auditing and review those results regularly.
Disable/drop: Disable the login first to ensure no dependent applications break, then drop it after a sufficient quarantine period.
Robust housekeeping will ensure these tasks are performed on a regular basis. If you need assistance with the above, or even a one-time database security assessment to ensure your documentation matches reality, our SQL Server database consultancy can help. Contact us to book a review of your estate, knowing that you’re in the safe hands of a Microsoft Partner.
Book your database security review
If this article resonates with areas of database security that you’re currently neglecting, then it might be time to get SQL Server database consultancy from the experts at Xynomix. As part of our onboarding and through detailed database security assessments, we highlight areas within your estate that need hardening. Similarly, if it’s time to move to a more current version, get in touch to discuss your SQL Server database upgrade pathway.
Get database support
Complete the form to get database assistance
"*" indicates required fields
