2

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
  • Can you disponibilish more informations about SQL Server Instance? Help>About>Copy Info – Leonardo Lacerda Nov 1 at 14:40
  • 1
    Sure: 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 – J.D. Nov 1 at 14:43
  • The user is disabled in print screen image. Try again after you enable user – Leonardo Lacerda Nov 1 at 14:46
  • I see that it has the X over it, but I don't think it's actually disabled, as my own account shows the same X over it under the Users node and I can still login and use that account (it's actually the one I'm using to administer permissions on this test account). Additionally, on another server the dbo user has the X on it too but there's a schema in the same database who's owner is dbo. – J.D. Nov 1 at 14:48
  • You tried with GUID in DBO? Try create schema with another user and Right click button DBO>Owned Schemas>"Select Schemas">OK. Showed same error? – Leonardo Lacerda Nov 1 at 14:54
1

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.