How to use defaultschema in MSSQL

General Discussion
1

I am new to Liquibase and I have a MSSQL database with multiple schemas and I set the defaultSchemaName in my property file but when I run an liquibase update I am getting the following error. Is defaultSchemaName supported for MSSQL? If yes, how can I resolve this issue? Thanks

Caused by: liquibase.exception.DatabaseException: java.lang.RuntimeException: Cannot use default schema name user on Microsoft SQL Server because the login schema of the current user (dbo) is different and MSSQL does not support setting the default schema per session.
at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:125)
at liquibase.integration.commandline.Main.doMigration(Main.java:1520)
at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:399)
at liquibase.Scope.lambda$child$0(Scope.java:166)
at liquibase.Scope.child(Scope.java:175)
at liquibase.Scope.child(Scope.java:165)
at liquibase.Scope.child(Scope.java:144)
at liquibase.integration.commandline.Main$1.run(Main.java:398)

2

Try to create a property file like this one:

changeLogFile: …/path/to/file/dbchangelog.xml
driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@192.168.0.22:1521/orcl
username: PRO
password: password
classpath: …/path/to/file/ojdbc6-11.2.0.3.0.jar
defaultSchemaName: your schema name

3

Thanks for your response. I am using SQL Server but I do have the defaultSchemaName in my property file and the error posted was what I got when I run the update.

4

Hi @twleo

Could you please share below details:

  1. Your liquibase.properties file
  2. Liquibase version you are using

Thanks,
Rakhi Agrawal

5

Below is my property file. Liquibase version is 4.4.0. Thanks

classpath=C:\Program Files\liquibase\lib\sqljdbc_9.2\enu\mssql-jdbc-9.2.1.jre15.jar
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:61466;instance=SQLEXPRESS;databaseName=License
username=username
password=pswd
changeLogFile=MasterChangeLog.xml
log-level=info

defaultSchemaName=user
liquibase.hub.mode=off

6

Twleo, MS defaults the schema at the user level so you need to set the schema of the user you are using on the database side.

ALTER USER [userName] WITH DEFAULT_SCHEMA=[targetDB]
GO

After you run the extraction remember to set the schema back to the previous default (often dbo)

ALTER USER [userName] WITH DEFAULT_SCHEMA=[dbo]
GO

Check the extracted files to make sure they are specifying the schema if you want to preserve it.

The other caveat is that you can only pull objects from a single schema at a time.