Category: Security

Louis Davidson gives us a procedure which provides effective rights for a principal:

In my blog Calculating a Security Principal’s Effective Rights. I built a view, named Utilty.EffectiveSecurity that you could query to fetch a security principal’s rights to objects in a database. In that blog I tested the code and showed how it works. Now I have taken this to the extreme and expanded the view to include all of the user’s security by finding all of their rights to all of the things that the get rights for.

The list of possible permissions you can fetch can be retrieved from:

SELECT DISTINCT class_desc FROM fn_builtin_permissions(default)   ORDER BY class_desc;

This returns the following 26 types of things that can have permissions assigned and returned by the sys.fn_my_permissions function:

Read on for the code.

Matthew McGiffen shows us a way to test if an account is a member of an Active Directory group:

As part of my job I manage a bunch of SQL instances for Development and Test.

Access is managed though Active Directory groups, so I rarely have to do anything regards managing permissions. Nonetheless I often get requests from people to give them access. This is usually for a new starter or someone who has moved from one team to another.

Of course, the answer is usually that they just need adding to the right AD group. Rather than assume though, I always get them to check before I pass the request on to the AD team. You never know, there could be something else wrong.

You can also use xp_logininfo if you want to go the other way and get all of the members of a particular group.

Jakub Szymaszek walks us through Virtualization Based Security memory enclaves in Windows Server 2019 and SQL Server 2019:

Today, we are super excited to announce that you can now try and evaluate Always Encrypted with secure enclaves in the preview of SQL Server 2019.

Always Encrypted with secure enclaves in SQL Server 2019 preview uses an enclave technology called Virtualization Based Security (VBS) memory enclaves in the upcoming version of Windows (Windows Server 2019 and Windows 10, version 1809), which is currently also in preview. A VBS enclave is an isolated region of memory within the address space of a user-mode process. The isolation of VBS enclaves is provided by the Windows hypervisor, which makes VBS enclaves appear as black boxes, not only to the processes containing them, but also all other processes and the Windows OS on the machine. Even machine administrators are not able to see the memory of the enclave. The below screenshot shows what an admin would get to see when browsing the enclave memory using a debugger (note the question marks, as opposed to the actual memory content).

The compliance regime is shifting toward preventing high-privilege users (DBAs, sysadmins, etc.) from accidentally or maliciously exposing sensitive information, so it makes sense that this is the primary security push.  I think that these changes are starting to make Always Encrypted a better option than a roll-your-own data encryption model.

Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions for other users:

Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing an entitlement review (checking to make sure everyone has the permissions they need, and just the permissions they need).

And, if you’ve made it this far, you read the first line and you know that the permission required is VIEW DEFINITION. What’s interesting is that this permission is usually used to grant someone the ability to look at the code behind T-SQL code objects. SPs, Views, Functions etc. But, it turns out that principals also have the VIEW DEFINITION permission.

Kenneth has a few notes for this as well, so check it out.