DbUp for sqlserver creates schemas under dbo authorization for non dbo user

Question

I'm trying to understand the underlying reason as to why DbUp upgrade creates schemas under dbo authorization, when the login/user in the connection string is neither an "owner", nor has "db_owner" role assigned.

To elaborate, let's say I manually create the following using the sa account:

Server login MyTestUser, which has only one server role - public.
Database MyTest.
Database user MyTestUser, which maps to the MyTestUser login, who initially owns no schemas and contains the following database roles: db_backupoperator, db_datareader, db_datawriter, db_ddladmin

Now, let's say I have the initial script where I create a schema:

DECLARE @User NVARCHAR(MAX)
SET @User = (SELECT CURRENT_USER)

DECLARE @Sql NVARCHAR(MAX)    
SET @Sql = 'CREATE SCHEMA [MyTestSchema] AUTHORIZATION ' + @User;
EXEC(@Sql)

If I login to the database using MyTestUser, and execute the SQL directly on the database, I get the desired result where the owner of the new schema is MyTestUser.

Now, whenever I run an upgrade using DbUp for SQL Server (the MyTestUser is specified in the connection string), the same schema is created under dbo - which is not what I want. This also happens for the initial schema that the DbUp creates for a fresh database. I'm using dbup-sqlserver 4.6.0 nuget.

The code to execute upgrade:

var upgradeEngine = DeployChanges.To
                    .SqlDatabase(databaseSettings.Value.ConnectionString, databaseSettings.Value.DefaultSchema)
                    .JournalToSqlTable(databaseSettings.Value.DefaultSchema, "SchemaVersions")
                    .WithScriptsFromFileSystem(scriptsPath)
                    .LogTo(upgradeLog)
                    .Build();

var result = upgradeEngine.PerformUpgrade();

And just for clarity, here's the connection string:

data source=localhost\sqlexpress;initial catalog=MyTest;User ID=MyTestUser;Password=SuperSecret;integrated security=True;MultipleActiveResultSets=False

I'm struggling to understand as to why there's a difference in the two processes, maybe someone could shed some light?

In this case, you can look at the source code. I made a quick lookup for `dbo` and I find [this](https://github.com/DbUp/DbUp/blob/master/src/dbup-sqlserver/Helpers/TemporarySqlDatabase.cs#L53) on the source code, maybe the author assumes the `dbo` scheme on its process. — Max, Aug 18 '23 at 11:14

score 0 · Accepted Answer · answered Aug 23 '23 at 13:42

Turns out this behaviour was due to my own unawareness of the connection string content. By setting integrated security=True I was telling the upgrader to use my windows credentials, thus ignoring the credentials specified in the connection string. Once I set that to false, eveything fell in order.

DbUp for sqlserver creates schemas under dbo authorization for non dbo user

1 Answers1