I am looking for a way to test if a SQL script will execute on a database without actually executing the script or having permission to execute the script. The reason for this is that my team writes code for a SQL database and we maintain that code in a DevOps repository. When we deploy changes to the database we create a SQL script and then provide that script to a DBA to run with db_owner permissions. Occasionally, we run into a situation where there is an error in the script and the script fails to execute when run by the DBA.

When the script fails, the DBA has to get back in touch with the developer so that the developer can diagnose the reason for the failure and then update the script so that it will execute. I am hoping to find a way to test that the script will execute successfully so that the developer can test it before providing the script to the DBA. I have tried running inside of a transaction that is rolled back, but if the script needs elevated permissions to run it will throw and error that permission is denied.