So I’m hosting T-SQL Tuesday this month and decided that since security is something everyone has to deal with it would make a fun topic for this month. I’d actually written this post before I wrote my hosting post for T-SQL Tuesday but it fit so well I couldn’t bring myself to not use it.

So can you deny a permission to a member of the db_owner role? Yep, appears you can. I’d always believed this was true myself but a simple test proves this wrong.

USE master
GO
CREATE LOGIN Kenneth_DBO WITH PASSWORD = 'Ken', CHECK_POLICY = OFF;
GO
USE Test
GO
CREATE TABLE DenyTest (Id int);
INSERT INTO DenyTest VALUES (1),(2),(3);
CREATE USER Kenneth_DBO FROM LOGIN Kenneth_DBO;
EXEC sp_addrolemember 'db_owner','Kenneth_DBO';
DENY SELECT ON DenyTest TO Kenneth_DBO;
GO

Now log in as Kenneth_DBO and run this test.

USE Test
GO
SELECT * FROM sys.user_token;
SELECT * FROM sys.fn_my_permissions('DenyTest','object');
GO
SELECT * FROM DenyTest;
GO
DELETE FROM DenyTest;
GO

Notice that sys.user_token shows we are indeed a member of db_owner. When we look at sys.fn_my_permissions for DenyTest, however, it shows lot’s of permissions but SELECT is not one of them. This should mean that the select permission isn’t there, but you never know until you try it out.

-- Try to SELECT
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'DenyTest', database 'Test2012', schema 'dbo'.
-- Try to DELETE
(3 row(s) affected)

When look at the output you notice that the SELECT was indeed denied but the DELETE worked just fine.

So why the myth in the first place? Well sysadmin can not be denied anything, and neither can dbo. I can just hear someone saying “Uh wait just a minute here. You showed that db_owner CAN be denied. Be consistent dude!” Well I am being consistent. The actual owner of the database (dbo) is different from a member of the db_owner role. Of course the only differences I know is that there can only be one dbo and it can’t be denied anything while there can be multiple members of the db_owner role and it can be denied.

But I don’t want you to take my word for it, we need an example!

First of all we have to remove the user Kenneth_Test from the database. The dbo cannot already be a principal in the database. That would mean the same SID would be used for two different database principals, and that’s not allowed. Next use sp_changedbowner to set Kenneth_DBO to be the DBO.

USE Test
GO
DROP USER Kenneth_DBO;
GO
EXEC sp_changedbowner 'Kenneth_DBO';
GO

Next we try to DENY Kenneth_DBO.

DENY SELECT ON DenyTest TO Kenneth_DBO;

and we get an error.

Msg 15151, Level 16, State 1, Line 11
Cannot find the user 'Kenneth_DBO', because it does not exist or you do not have permission.

Wondering why? It’s because the principal Kenneth_DBO doesn’t exist. We assigned it to dbo. If you are interested you can prove it with this query.

SELECT Logins.name AS Login_Name, Users.name AS User_Name
FROM sys.server_principals Logins
JOIN sys.database_principals Users
ON Logins.sid = Users.sid
WHERE Logins.name = 'Kenneth_DBO';

So let’s try to DENY dbo.

DENY SELECT ON DenyTest TO dbo;

This time we get the very self explainitory error:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

So there you go. You can DENY a permission to members of the db_owner role, but not to the database owner.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tuesday Tagged: database permissions, microsoft sql server, security