SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4863 Visits: 2021
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?



Sue_H
Sue_H
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: General Forum Members
Points: 83017 Visits: 16795
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
Chris Harshman
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39476 Visits: 7335
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
Sue_H
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: General Forum Members
Points: 83017 Visits: 16795
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
Jeff Moden
SSC Guru
SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)

Group: General Forum Members
Points: 935726 Visits: 49074
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4863 Visits: 2021
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
ZZartin
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28128 Visits: 18078
What are you trying to fix? dbo is the default owner of those roles.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)SSC Guru (935K reputation)

Group: General Forum Members
Points: 935726 Visits: 49074
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search