Liquibase cannot perform SELECT on Azure SQL Server

Luis Daniel Mesa Velasquez 0 Reputation points
2024-04-08T22:04:10.1366667+00:00

I'm trying to automate the whole process of provisioning by using Liquibase. I have divided it in 3 steps.

  1. Connect as administrator to master database in Azure SQL Server to create logins
  2. Connect as administrator to my database to create users for the logins (created in step 1) with permissions
  3. Connect as a user with db_ddladmin permission (created in step 2) to create the tables

Liquibase CAN create the tables in step 1 (both DATABASECHANGELOG and DATABASECHANGELOGLOCK), then it tries to do a SELECT COUNT(*) FROM DATABASECHANGELOGLOCK but, since they're both in dbo, it fails since even though I'm administrator because I cannot perform SELECT.

  1. How do I get the SELECT, INSERT, UPDATE, DELETE grants for those 2 tables?
  2. Is this possible? Why do I have permission to create tables but not to read them?
  3. Can I select my user's catalog / schema from the JDBC connection string so it allows me to have the SELECT, INSERT, UPDATE, DELETE grants on those 2 tables?

If I perform the first step manually (creating just the logins without liquibase), steps 2 and 3 run smoothly. I end up with 4 tables, DATABASECHANGELOG and DATABASECHANGELOGLOCK in my dbo schema for my database and DATABASECHANGELOG and DATABASECHANGELOGLOCK in the app's schema for the DDL statements.

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 27,642 Reputation points Microsoft Employee
    2024-04-09T16:55:28.79+00:00

    @Luis Daniel Mesa Velasquez Welcome to Microsoft Q&A thanks for posting your question.

    SQL Server has / uses granular permission model. First, db_ddladmin only grants permissions to create or modify objects. This doesn't mean that you can read or modify data in those objects. So, a database user that is member of db_ddladmin can create a table but cannot insert or select from it. This is expected & by design.

     Database-Level Roles - SQL Server | Microsoft Learn -- See the table & image in this topic. 

    • How do I get the SELECT, INSERT, UPDATE, DELETE grants for those 2 tables? 

    If you want to get these permissions in the context of a session, then use sys.fn_my_permissions with the appropriate securable name and securable class. 

    sys.fn_my_permissions (Transact-SQL) - SQL Server | Microsoft Learn 

    You can also get the permissions for any user on database objects using sys.database_permisisons. 

    sys.database_permissions (Transact-SQL) - SQL Server | Microsoft Learn -- Example B or C 

    Other than this, I have no idea what Liquibase does or doesn't do underneath the covers. I would recommend reaching out to the Liquibase documentation.

    I hope this information helps.

    Regards

    Geetha