Behavior of DLM Automation VS SQL Change Automation
dlhpaycor
Posts: 1 New member
We are in the process of upgrading to SQL Change Automation from DLM Automation. The only real issue we're facing at the moment is the behavior or grants when a role is NOT checked in.
In this scenario we use SQL Source Control to check in changes and in this repository the DEVELOPER role is NOT checked in. The DEVELOPER role had a grant view definition manually applied by DBA's but the grant is NOT checked in to source control either.
When using DLM Automation (And SQLRelease prior to that) that grant shows up in the comparison report that it will drop, but in the script it does NOT actually execute the drop because the role is not checked in, if the role gets checked in at some point, then it will actually execute the drop.
When using SQL Change Automation the drop shows up AND will be executed (shows up in the change script).
We currently rely on roles not being checked in for anything the DBA's manually do or anything that we don't want to deploy. We do want to continue deploying grants to roles just only when they are actually checked in.
Is the expected behavior with SQL Change Automation that grants to roles will be dropped even if the role itself isn't checked in?
In this scenario we use SQL Source Control to check in changes and in this repository the DEVELOPER role is NOT checked in. The DEVELOPER role had a grant view definition manually applied by DBA's but the grant is NOT checked in to source control either.
When using DLM Automation (And SQLRelease prior to that) that grant shows up in the comparison report that it will drop, but in the script it does NOT actually execute the drop because the role is not checked in, if the role gets checked in at some point, then it will actually execute the drop.
When using SQL Change Automation the drop shows up AND will be executed (shows up in the change script).
We currently rely on roles not being checked in for anything the DBA's manually do or anything that we don't want to deploy. We do want to continue deploying grants to roles just only when they are actually checked in.
Is the expected behavior with SQL Change Automation that grants to roles will be dropped even if the role itself isn't checked in?
Tagged:
Answers
Hi @dlhpaycor,
To give you a bit of initial information, the expected behavior here is that the role would be dropped and not just the grant. If however, only the grant is going to be deployed the role must be in the project folder.
I'm also not aware of this behavior changing from DLMA to SCA.
The best way to prevent this from happening is to use a filter file that excludes the grants you don't wish to check-in, as you mentioned you don't want to check them in, so I'd advise setting this in SQL Source Control, bear in mind that if you have these roles in the temporary database, you will need to use an explicit filter in the Invoke-DatabaseBuild (or Build) step to prevent the roles from being dropped during the cleaning stage
Alternatively, if this is just during the release, you can just use the filter explicitly on the New-DatabaseReleaseArtifact step
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?