Consequences of making "dbo", directly, the owner of db_datareader schema

Question

Consequences of making "dbo", directly, the owner of db_datareader schema

goher2000

SSCertifiable

Points: 5477
More actions
February 23, 2018 at 1:02 pm

#343728

For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

goher2000 - Friday, February 23, 2018 1:02 PM
For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?

You can change the owner of the schema but you won't be able to drop the role. You can't drop fixed database roles.

Sue

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

goher2000 - Friday, February 23, 2018 1:02 PM
For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?

What are you trying to achieve? Typically you wouldn't be changing system created objects like this, and that schema won't have any objects in it anyway.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Chris Harshman - Friday, February 23, 2018 2:37 PM
goher2000 - Friday, February 23, 2018 1:02 PM
For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?
What are you trying to achieve? Typically you wouldn't be changing system created objects like this, and that schema won't have any objects in it anyway.

You can create objects in those schemas and work with them like other objects - you can't see them but they are there:

CREATE TABLE [db_datareader].Test (SomeID int);

SELECT [name], SCHEMA_NAME(schema_id) as SchemaName FROM sys.objects WHERE [name] = 'Test';

SELECT [name], SCHEMA_NAME([schema_id])as SchemaName FROM sys.tables WHERE [name] = 'Test';

INSERT [db_datareader].Test values (2);

SELECT * FROM [db_datareader].Test;

Sue

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

goher2000 - Friday, February 23, 2018 1:02 PM
For the fixed database roles such as db_datareader, by default the role and a schema of the same name are created. The owner of the role is "dbo" and that role owns the same-named schema. What would happen if I made "dbo", directly, the owner of the db_datareader schema and deleted the db_datareader role?

There's no reason to ever do such a thing. I'm with the others in asking why you would ever want to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goher2000 SSCertifiable Points: 5477 More actions · Answer 5

Well I have a database where owner of db_datareaer and db_datawriter is dbo, not sure why is that (some genius DBA I suppose), I want to fix it however I am trying to figure out what impact it will have on the app.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 6

What are you trying to fix? dbo is the default owner of those roles.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

Your original posts states that you want to delete the db_datareader role after assigning it's schema to DBA. Since it's a database fixed role, I'm not sure that you could delete it (I've not tried and probably won't because there's no reason to do such a thing) but, if you could delete it, I'm thinking that some seriously bad stuff is going to happen with your security because the system will check for the role while do other things (like normal login management, etc) and, when it can't find it, there will be forms of flying Kimchi that will make you cry out loud.

To summarize, don't even think of doing this

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)