Category: Security

Jon Russell breaks the chain:

Designing a reporting layer that protects sensitive data takes more than hiding tables behind a view. You must understand how schema permissions and ownership chaining interact, or a well‑meant deny can suddenly block your users—or worse, let them see columns you thought were private. The walk‑through below shows the entire life‑cycle of a common scenario:

1. Build an HR table that holds confidential columns.
2. Expose a summary view in a separate schema.
3. Grant a reporting role access to the view but explicitly deny access to the HR schema.
4. Break the ownership chain, observe the failure, diagnose the cause, and repair it by realigning ownership.

Read on to learn more. Note that this is all within a single database, so we aren’t talking about the cross-database ownership chaining setting. That setting being on immediately raises red flags for me.

Comments closed

Steve Stedman breaks down some myths around Transparent Data Encryption in SQL Server:

Transparent Data Encryption (TDE) in SQL Server is a powerful feature designed to protect data at rest by encrypting database files. While TDE is an excellent tool for securing data stored on disk, it’s often misunderstood, leading to assumptions about its capabilities. In this blog post, we’ll clarify what TDE does not do to help you better understand its scope and limitations, ensuring you implement the right security measures for your SQL Server environment.

Click through for the list.

Comments closed

Mike Walsh explains why it’s important to keep up to date on patches:

At first, I was a bit shocked, but then I realized it actually makes sense and it matches a regret I hear far too often from folks who call us too late: “If we don’t change too much, we’re safe.”

Sadly, nothing can be further from the truth. When a software vendor releases a fix for a vulnerability, it certainly could be caused by a recent patch or update – but more often than not that fix is for a longstanding issue that has been until recently unknown to the world.

Read on for Mike’s argument. This also applies to people running versions of SQL Server no longer receiving patches (hint: 2016 SP3 and earlier). Just because there isn’t a patch doesn’t mean there isn’t a vulnerability.

Comments closed

Mike Walsh takes us through a recent CVE entry:

On patch Tuesday this week, Microsoft released an Important severity security update (a CVSS base score of 7.5)

The details of this 0-day exploit are available to read at the NIST site, and the Microsoft security update site.

In short, the exploit that Microsoft has discovered and subsequently fixed can allow information disclosure.

The NIST entry is kind of a joke right now, and the Microsoft security update info is basically what they submitted to NIST plus links to download the patches. Still, this is worth patching and it’s an issue that goes back at least to 2016—probably earlier, but 2016 is the last version of SQL Server that still gets security updates.

Comments closed

Jon Russell takes a look at several ways to gauge user permissions:

Understanding and troubleshooting SQL Server permissions can be challenging, especially when direct grants, role inheritance, ownership chains, and explicit denies all interact. The six scenarios that follow show how the engine decides who can do what, then demonstrate the diagnostic steps that reveal why it made that decision. Each section provides a setup script you can run in a dedicated test database, followed by a diagnostic query and a short explanation of the result.

Click through for six methods. I do wish that, instead of sys.fn_my_permissions() there was some sys.fn_user_permissions(@UserName) option. I realize that you can execute as a specific user and then run the function, but I had dreamed for years about having a way to track effective user permission changes, and sys.fn_my_permissions() requires not only that you have the authority to execute as a specific user, but also that you know all of the relevant users.

Comments closed