April 26, 2017 at 10:20 am
Could someone help me understand why it is best practice for 'dbo' to own user-created database schemas? What are the downsides to not having 'dbo' own the user created schemas? Thanks for your help!
April 26, 2017 at 12:04 pm
The dbo is generally considered to be the 'sa' of the database. Just as 'sa' can access the whole server, 'dbo' can access the whole database. Generally speaking, this is a construct, not a requirement. However, it's how I've usually designed my databases. I divorce security and schema's completely so that any given group or role (or individual user, but I avoid those) can get the correct access to a given schema or schema's. The management and control of the database is through dbo. That way I don't have to do anything funky for system processes to get at tables and indexes. Otherwise, you may have to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2017 at 12:20 pm
Thanks for your help. So, no problems if the owner is assigned to an ID in the domain or even locally and then that ID is removed at some point?
April 26, 2017 at 12:35 pm
Well no. If you assign ownership to a login and that login goes away, it could cause issues. You can control the security so that people can only access a given schema and give them different permissions within the schema or between schemas, but I'd make the owner the dbo for everything. You avoid issues that way. Although, again, this is not a requirement. It's just a good method to ensure consistency of behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2017 at 1:01 pm
adaml.hardman - Wednesday, April 26, 2017 10:20 AMCould someone help me understand why it is best practice for 'dbo' to own user-created database schemas? What are the downsides to not having 'dbo' own the user created schemas? Thanks for your help!
One concept of having the same owner for different schemas is Ownership Chaining, meaning if two schemas have the same owner, then there is implicit permissions between the two schemas. If they have different owners, you have to explicitly grant permissions between objects in the separate schemas for them to work together:
https://www.mssqltips.com/sqlservertip/1778/ownership-chaining-in-sql-server-security-feature-or-security-risk/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply