Resource for Ignoring Database Permissions

vfrank66

Is there a good resource for dealing with Database Source Control permissions.

I am trying to run "Redgate DLM Automation" task in VSTS, it keeps failing saying a role has not been created. I believe my original check had no filters setup or someone checked in with no filters. I have added comparision options and filters so as no permissions should be checked. But it is still failing because those permissions are already checked. 

Since I don't know the best way to handle this in a lab environment I am seeking a resource and how to setup redgate source control ignoring permission's and how to stop someone from accidentally checking in permissions. 

My permissions change per environment and I have the scripts to create these permissions, but I am struggling to see the point in Redgate deployment where to add these scripts and how to prevent someone from introducing bad security.

Sergio R

You can apply SQL Compare options and filters at any stage in the process, not just when checking in.

So if for some reason you check in permissions you can remove those in DLM Automation by applying the correct permissions or filter.

If you apply a filter in SQL Source Control, that filter will be stored in the repository and applied automatically in DLM Automation, however the DLM Automation cmdlets allow you to explicitly set a filter path to override the default filter or apply one if there isn't a default.

Please bear in mind that by default DLM Automation already has the option to "Ignore User Permissions and Role Memberships" enabled by default.

https://documentation.red-gate.com/dlma2/using-sql-compare-options-with-dlm-automation-cmdlets/default-sql-compare-options-used-by-dlm-automation-cmdlets

Sergio R

Here's a couple of posts on that subject that should help you:

https://www.red-gate.com/hub/product-learning/sql-source-control/source-controlling-database-permissions
http://workingwithdevs.com/source-controlling-database-users/ 

vfrank66

I have read that first article. But okay here is an example, mistake by a teammember of mine and they checked in permissions, while the DLM Automation task to build a database package fails because the role does not exist on an object whose permissions were checked in.

What should be done to prevent that? Check in filters? What if the dev mistakenly doesn't have them, what is the recommendation on this?