How to set AUTHORIZATION on CREATE SCHEMA

Question

I'm looking to create a new schema for an existing database, which currently only uses the dbo schema. I'm after advice on how to set the AUTHORIZATION (owner name) and what affect this will have.

1. Should I create a new user for this or can I use the dbo user?

I notice the existing Database Roles in the database are owned by the dbo user.

2. If I set the AUTHORIZATION to a new user will I need to create new Database Roles owned by the new user?

I've also noticed the Default schema for existing Users is the dbo schema.

3. Will I need to create new Users with a default schema set to the new schema?

My main reason for creating the new schema is to separate the database objects by name rather than for permissions. I intend on granting certain existing Users access to specific database procedures on the new schema as we do for database procedures on the dbo schema.

Answer 1

You do not need to create a new user to be owner of the schema.

The default dbo owner is fine, especially if your adding the schema for organization reasons rather then security.

You do not need to create new users to access the new schema.

The only thing I would add is to make sure if you do change the owner from dbo, you change it to a domain service account or other permanent sql account. If the user gets deleted it can cause issues accessing the schema.

Answer 2

Just create each schema, leave the owner as .dbo unless you have another permanent 'SA' type account that you wanted to house them under. Then just assign access to them accordingly by user name. You don't need to create anything other than the schema you want and assign the rights.

Answer 3

I appreciate the other answers but am posting an answer now to pass on the understanding I now have from various sources:

1. Should I create a new user for this or can I use the dbo user?

As per JasonBluefire's answer the dbo user may be used when adding the schema for organisational reasons rather than as segregation for security purposes. Once the schema is created this relationship may be seen using the following query:

SELECT s.schema_id, s.name, 
    dp.principal_id, dp.name, dp.type_desc
FROM sys.schemas s 
INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id

2. If I set the AUTHORIZATION to a new user will I need to create new Database Roles owned by the new user?

The only significance of the dbo ownership of the database roles is that "The owner of the role, or any member of an owning role can add or remove members of the role.". See CREATE ROLE MS documentation. There's therefore no need to create new versions of the existing database roles.

3. Will I need to create new Users with a default schema set to the new schema?

The only significance of the default schema being set to dbo for existing database users is that it will be "the first schema that will be searched by the server when it resolves the names of objects for this database user.". See CREATE USER MS documentation. There's therefore no need to create new users.