Different Grant Permissions in each environment is causing conflicts .. how to fix or work around?

salexander

We use different SQL Users in our various environments, so when for example create a stored procedure in our lowest Dev region and grant the appropriate permissions the code for the procedure saves these permissions.  I like this, but problem is the Dev user will not be applicable to any other region.  So it would have this at the end:

GO 
-- Permissions

GRANT EXECUTE ON  [dbo].[uspSystemImportLog] TO [MyDomain\DevWebApp1]

GO

Problem is [MyDomain\DevWebApp1] would only be applicable in Dev yet this is what gets saved to the Git repo. How can I either exclude permissions or set permissions so it's more like a variable so as I push it through the environments it sets to something that is applicable?  Maybe @WebAppUser@ in source control and this is replaced dynamically as it moves up through Dev Test, Staging, and Prod...  As it stands now Git will have the Dev users and always show a conflict with all the other environments.  Thanks.

Jessica R

Hi and thanks for your post!

Just to confirm, are using SQL Change Automation with a SQL Source Control project?

If so, you can ignore permissions using comparison options. Please see here for more detail on how to change the comparison options: https://documentation.red-gate.com/soc7/configuring/change-the-comparison-options