Challenge Accepted:
Grant Fritchey ( b | t ), in his infinite wisdom, has created a challenge; a random blog post challenge all to do with the featured image at the top of this post:
Actually, from his blog post, his actual reason was…
for no particular reason at all or maybe because I had too much food at lunch or just because Jason Hall gave me the idea
Now to me, that is a cheeky response, which is fitting because this seems like a cheeky gif… as in it is saying “oh hey!” in response to someone asking “Who did this?!”.
So taking this into account, I’ve decided to document some cheeky behaviour with permissions in SQL Server.
Setting Ourselves Up for Cheekiness:
Let us create a super secret table, with a super secret column containing a super secret value that only we should be able to update.
USE Pantheon; GO CREATE TABLE dbo.SuperSecretContent ( ID int IDENTITY(1, 1) ,SecretColumn varchar(50) ); GO INSERT INTO dbo.SuperSecretContent (SecretColumn) SELECT RIGHT(NEWID(), 50); GO
Yeah…I know, my super secret value is a NEWID()
…I’m not sorry.
Enter Stage Left:
We also have a random LOGIN and USER that we didn’t create but we somehow have the create scripts for…
USE [master]; GO CREATE LOGIN [PhysicianWhom] WITH PASSWORD = 'A_Rapid_Hedgehogs_Wrench'; GO USE [Pantheon]; GO CREATE USER [PhysicianWhom] FROM LOGIN PhysicianWhom; GO
Fade To:
One day we check out our super secret value and we see:
SELECT SecretColumn FROM dbo.SuperSecretContent; GO

Dammit! He must have access to db_datawriter
so he can update all user tables. I’ll do a quick check just to make sure!
SELECT Users.[name] AS UserName , DBRoles.[name] AS RoleName FROM sys.database_principals AS Users INNER JOIN sys.database_role_members AS RoleMembers ON Users.principal_id = RoleMembers.member_principal_id INNER JOIN sys.database_principals AS DBRoles ON DBRoles.principal_id = RoleMembers.role_principal_id WHERE Users.[name] = N'PhysicianWhom'; GO

Ha! I know how you did it now. You’re not going to be in that database role for long! If he needs update access to other tables then we can grant UPDATE
permissions on those tables individually.
Let’s remove him and then refresh our table.
-- Role ALTER ROLE db_datawriter DROP MEMBER PhysicianWhom; GO TRUNCATE TABLE dbo.SuperSecretContent; GO INSERT INTO dbo.SuperSecretContent (SecretColumn) SELECT RIGHT(NEWID(), 50); GO

Happily Ever After?
Time passes and we check our value again:

Arrgh!!! How? Wait, do you have UPDATE
permissions on my table?
SELECT Users.[name] AS UserName , Perms.[permission_name] AS PermissionGranted , Perms.class_desc AS PermissionOn , Obj.[name] AS ObjectName , Perms.state_desc AS PermissionState FROM sys.database_principals AS Users INNER JOIN sys.database_permissions AS Perms ON Perms.grantee_principal_id = Users.principal_id INNER JOIN sys.objects AS Obj ON Obj.[object_id] = Perms.major_id WHERE Users.[name] = N'PhysicianWhom'; GO

This is no one’s fault but my own. Removing them from the db_datawriter role was a shotgun approach, I need to be more precise. So I’m going to DENY
him update access to my table and then the problem will be over.
DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom; GO TRUNCATE TABLE dbo.SuperSecretContent; GO INSERT INTO dbo.SuperSecretContent (SecretColumn) SELECT RIGHT(NEWID(), 50); GO

Paranoia Kicks In:
He can’t have updated it…right?
I mean, he doesn’t have db_datawriter permission; I’ve denied him update permission on my table. I’m safe!…right?

[It’s at this point I went and got a cup of coffee..]
But I denied him! I denied him!

No! He will not win! I am determined…but lost? In my desperation, I scour Books Online and chance upon this little nugget from BOL…
A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.
…and a small ray of light shines through.
I make a quick modification to my permissions script…
SELECT Users.[name] AS UserName , Perms.[permission_name] AS PermissionGranted , Perms.class_desc AS PermissionOn , Obj.[name] AS ObjectName , Perms.state_desc AS PermissionState , ( SELECT [name] FROM sys.columns AS c WHERE c.[object_id] = Obj.[object_id] AND c.column_id = Perms.minor_id) AS ColumnPermission FROM sys.database_principals AS Users INNER JOIN sys.database_permissions AS Perms ON Perms.grantee_principal_id = Users.principal_id INNER JOIN sys.objects AS Obj ON Obj.[object_id] = Perms.major_id WHERE Users.[name] = N'PhysicianWhom'; GO

Righting A Terrible Wrong
How do I fix this?
A deny is not going to work; we’ve already tried that.
Adding him to db_denydatawriter
is not going to work. What happens if he needs to update other tables in our database?
But if we GRANT him access on our entire table then that will overwrite our column level GRANT. Then we can DENY him on our table and all is write…I mean right with the world!
GRANT UPDATE ON OBJECT::dbo.SuperSecretContent TO [PhysicianWhom] DENY UPDATE ON dbo.SuperSecretContent TO PhysicianWhom; GO

EXECUTE AS LOGIN = 'PhysicianWhom'; SELECT USER_NAME() AS WhoooAreYou; UPDATE dbo.SuperSecretContent SET SecretColumn = 'Are you listening to me?' WHERE 1 = 1; GO SELECT SecretColumn AS UpdatedTo FROM dbo.SuperSecretContent; GO REVERT; GO

A Little Parting Gift:
A little present for all you “deprecated means nothing” people out there.
I’m just going to highlight a section here of relevance:
A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for the sake of backward compatibility. It will be removed in a future release.
I aimed to get this blog post out yesterday but when I ran this on…
Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )
…then my table-level DENY DID take precedence over my column-level GRANT.
When did they introduce that? Was this mentioned and I just missed it? Seriously give it a go and see for yourself!
A Little Parting Gif:

I’m not positive where you’re getting your information, however good topic. I must spend a while learning more or figuring out more. Thank you for wonderful information I used to be looking for this information for my mission.
You really make it appear so easy along with your presentation but I in finding this topic to be really something that I believe I might by no means understand. It seems too complex and extremely large for me. I’m looking ahead for your subsequent submit, I’ll attempt to get the cling of it!