How can we help you today? How can we help you today?
Ganesh Nerella

Activity overview

Latest activity by Ganesh Nerella

If you're looking to run   Flyway CLI inside a Docker container   to compare a   schema model (SQL scripts)   with   multiple target environments   and auto-generate migration scripts, here’s a reusable setup template that should help: Folder Structure   ``` flyway-project/ ├── sql/                   # Folder with your schema model scripts │   ├── V1__init.sql │   └── ... ├── conf/ │   ├── flyway-dev.conf │   ├── flyway-qa.conf │   └── flyway-prod.conf └── docker-compose.yml     # Optional if you want to orchestrate ``` --- Docker Run Command (per environment)   ```bash docker run --rm \  -v $(pwd)/sql:/flyway/sql \  -v $(pwd)/conf:/flyway/conf \  redgate/flyway \  -configFiles=conf/flyway-dev.conf \  info ``` You can replace `info` with `migrate`, `validate`, or `repair` as needed. --- Sample `flyway-dev.conf` File   ```ini flyway.url=jdbc:sqlserver://your-dev-db-host:1433;databaseName=YourDB flyway.user=your_username flyway.password=your_password flyway.locations=filesystem:/flyway/sql flyway.baselineOnMigrate=true flyway.outOfOrder=true ``` Create similar config files for `QA`, `Staging`, `Production`, etc. --- Generating Migration Scripts   Flyway doesn’t generate a migration  diff  script by default like Redgate SQL Compare, but here’s a workaround: If you have:  a baseline script in `sql/`  new changes in separate SQL files (e.g., `V2__add_table.sql`), Then, Flyway will: 1. Validate target DB vs. your model scripts 2. Apply new scripts in order (or just output if using `info` or `validate`) For scripting without applying: ```bash docker run --rm \  -v $(pwd)/sql:/flyway/sql \  -v $(pwd)/conf:/flyway/conf \  redgate/flyway \  -configFiles=conf/flyway-qa.conf \  -dryRunOutput=/flyway/sql/dryrun_qa.sql \  migrate ``` --- Looping Through Multiple Environments (Optional Bash Loop)   ```bash for env in dev qa prod; do  docker run --rm \    -v $(pwd)/sql:/flyway/sql \    -v $(pwd)/conf:/flyway/conf \    redgate/flyway \    -configFiles=conf/flyway-$env.conf \    info done ``` --- Tips  1.) Keep your `sql/` folder under source control. 2.)  Use semantic versioning in file names (`V3__feature_X.sql`) to ensure order.  3.) For full schema diff and advanced modeling, you can pair Flyway with   Redgate SQL Compare CLI   as a pre-step.   Regards, Ganesh Nerella  Sr. Database Administrator Redgate Community Ambassador   / comments
If you're looking to run   Flyway CLI inside a Docker container   to compare a   schema model (SQL scripts)   with   multiple target environments   and auto-generate migration scripts, here’s a reu...
0 votes
You're definitely not alone—this is one of those subtle differences between on-prem SQL Server and Azure SQL Database that catches people off guard. In SQL Server 2022, if your stored procedure and the underlying tables are owned by the same user (like dbo ), you get the benefit of ownership chaining. That means users can run the stored proc without needing direct SELECT permissions on the tables—it just works. But in Azure SQL Database, especially when using Entra ID (formerly Azure AD) authentication and contained database users, that ownership chain can break down. Even if your schema and permissions are identical (as confirmed with SQL Compare/Data Compare), Azure SQL might still require explicit SELECT permissions on the table. What's odd in your case is that SELECT is failing, but DELETE and UPDATE are not. That makes me wonder: Is the SELECT being done on a view, or maybe through dynamic SQL inside the proc? Both of those can bypass ownership chaining. Are the proc and the tables definitely owned by the same principal (check with sys.objects or OBJECTPROPERTY() just to be sure)? Is the proc running under the caller’s context, or using something like EXECUTE AS ? You could try: Adding EXECUTE AS OWNER to the proc definition Explicitly granting SELECT on the table to the Entra ID user or role (just to confirm it resolves the error) Double-checking that the login context in Azure really maps the same way as it does in SQL Server In short: you're not wrong—your approach aligns with least privilege and standard best practices. Azure SQL just enforces things a bit differently due to how it handles identity and containment. Would love to hear if you find a clean workaround! Check Object ownership : SELECT     o.name AS object_name,    o.type_desc,    s.name AS schema_name,    u.name AS owner FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.database_principals u ON o.principal_id = u.principal_id WHERE o.name IN ('YourProcName', 'YourTableName');   Try redefining the stored procedure with EXECUTE AS OWNER to enforce the proper context: CREATE OR ALTER PROCEDURE YourProcName WITH EXECUTE AS OWNER AS BEGIN    -- your logic here END To confirm it’s a permissions issue, you can temporarily grant SELECT directly: GRANT SELECT ON dbo.YourTableName TO [your_EntraID_user_or_role];   / comments
You're definitely not alone—this is one of those subtle differences between on-prem SQL Server and Azure SQL Database that catches people off guard.In SQL Server 2022, if your stored procedure and ...
0 votes