TSQL Tuesday #63: – DBA Myths: You can’t deny something to a member of db_owner database role

7

February 10, 2015 by Kenneth Fisher

T-SQL TuesdaySo 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

DenyDBO1

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';

DenyDBO2

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.

7 thoughts on “TSQL Tuesday #63: – DBA Myths: You can’t deny something to a member of db_owner database role

  1. davidbainbridge83 says:

    I have always conflated dbo and db_owner.

    SQL offers a bit more granularity than I thought.

    • SQL will actually let you go down to column level permissions. I don’t see that done often though.

      • Anders Pedersen says:

        I have seen column level permission taken to the extreme! Worked on one case where they ran out of the ability to give more column level permissions, which I believe at the time was 192 columns…. (SQL 6.5 sp3). Basically each employee in that company had a column only they had access to in a table…….. I tried to give them a few alternate ways of doing it, but they did not want to hear it.

        • I hate it when that happens. What they are doing isn’t working well. So you give them an alternative, but they are just so stuck with what they have they don’t want to listen.

  2. […] Kenneth Fisher (me)(b/t) For my own contribution I discussed the reality of denying permissions to db_owner vs dbo. […]

  3. Joe Caldwell says:

    I always thought dbo was the schema, which was just called dbo by default.

    • There is a schema called dbo. Typically it’s the default schema. Dbo is also a user within the database and the “database owner”.

      The schema dbo comes from the fact that prior to schemas objects had “owners” that were a specific user. And you guessed it, the default owner for all objects within a database was dbo. When they converted to schemas dbo remained the name of the default schema. It can get a bit confusing I’ll admit :).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: