Are you saying that permissions have definitely not been changed?

Have you got multiple versions of PowerShell installed? Eg 5.x and 7.x

Sounds like there may be an error occurring which is forcing the window to close despite the Sleep.

Try this instead:

Read-Host -Prompt "Press any key to continue"

though I don't expect it...

A source control repository contains a (technical term) bunch of files which form some sort of logical grouping. That grouping is your choice.

An example repo would be 'Databases'. This could...

I used to work that way about 15 years ago.

Here is a sample process developing using SSDT. It assumes that devs do development locally.

1. Dev checks out a new Git...

That is rather a large topic! But SSDT allows you to create a 'project' for each of your databases. These projects are contained in one or more 'solutions'.

Each project contains...

This has happened to me several times, and it's a pain.

But uninstalling and reinstalling 'Microsoft SQL Server Integration Services Projects' and SSDT has worked in fixing it.

Next step would be...

Deploying using SSDT takes care of this for you. Have you considered using that?

Another possibility would be to snapshot your UAT database prior to deploying and do a revert in...

Does this work? Likely not, as XML things tend to behave differently from how you would expect.

SELECT *
FROM TEST.SDE.label l
WHERE EXISTS
(
    SELECT 1
 ...

You could put the statement 'pause' at the end of your script. It will prevent the window auto-closing and maybe there will be some sort of informational message there.

What happens when you try this ... is there an error?

But this will be hosted on production server where there are all other databases get backed up and patching etc. I agree that we should not support a database...

No problem.

Just picking up on one point in your last comment, I 100% agree with using stored procs to implement business logic on the SQL Server side. I mostly use...

Thank you for the screenshots, that helps explain what is going on. But I don't think I can help. I do not understand why it is complaining about SQL syntax...

There are reasons for separating logic into multiple packages, even though it may seem less tidy and requires more config up front.

Number 1 is around error handling and the debugging...