Sql-server – Why can’t I create a new schema with the owner set to dbo

permissionsschemasql serversql-server-2016users

I created a test account for checking permissions against.
It currently has the db_datareader, db_datawriter, and db_ddladmin roles for the Northwind database.
But when it tries to create a new schema on Northwind, the creation fails if it tries to authorize dbo as the schema owner, what's up?

Create Schema Failed

Version: 
SQL Server Management Studio: 15.0.18183.0 
Microsoft Analysis Services Client Tools: 15.0.1487.0 
Microsoft Data Access Components (MDAC): 10.0.18362.1 
Microsoft MSXML: 3.0 6.0 
Microsoft Internet Explorer: 9.11.18362.0 Microsoft 
.NET Framework: 4.0.30319.42000 
Operating System: 10.0.18362

Best Answer

The db_datareader, db_datawriter, and db_ddladmin permissions will not give the necessary privilege for this action.

db_datareader: Members of the db_datareader fixed database role can read all data from all user tables.

db_datawriter: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_ddladmin: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

As you can see, these permissions will give you privileges to create and manipulate some database structure. However, they will not give you the privileges to alter other user or create schema.

Check this image from Microsoft of database level roles and permissions enter image description here

Note that permission db_ddladmin does not grant you the privilege to create SCHEMA. To do this you need set your test user as a membership of db_accessadmin. This role will grant privileges to create schema and alter any user. The alter any user privileges will be necessary to set dbo user as a schema owner:

USE [your_database]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [teste]
GO

After this, you will can authorize DBO in the new schema like:

USE [your_database]
GO
CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO

Another role will grant permissions to do this is the db_owner, but is "some powerful" permission to this. That is why the correct role is db_accessadmin. You can read more about each permission at this link

Remember to use the commands in the correct database with:

USE [your_database]
Related Question