Creating schema in VS+SSDT without AUTHORIZATION clause

Question

I am using Visual Studio Community 2015 with SSDT 14.0.6121.0. I created SQL Server Database Project and added a Schema object. Inside of this, I wrote

    CREATE SCHEMA [MySchema]

and created a project snapshot. However, when I deploy this DACPAC (using DacFX), I see, that the actual deploy statement is

    CREATE SCHEMA [MySchema] AUTHORIZATION='dbo'

It's always 'dbo', regardless of actual executing account, and it leads to deploy failure due to lack of permissions in some cases.

In DACPAC, code is as follows:

    <Element Type="SqlSchema" Name="[MySchema]">
        <Relationship Name="Authorizer">
            <Entry>
                <References ExternalSource="BuiltIns" Name="[dbo]" />
            </Entry>
        </Relationship>
    </Element>

Is there a way to tell SSDT, that I don't want AUTHORIZATION to be 'dbo' or any other account?

Ed Elliott Ed Elliott · Accepted Answer · 2017-03-03T10:13:44

No - when SSDT needs to create a schema, it explicitly adds the authorization header and adds the user who owns it - if no one owns it then it puts it as dbo. There is nothing in SSDT to control this and it just does it for you.

Generally it is better to be explicit about these things and add a user who will own it, why can't you specify a user?

Creating schema in VS+SSDT without AUTHORIZATION clause

1 Answers