Users roles got cleared after deployement using SQL Change Automation:Release task in Azure DevOps

Kattesang

I am doing POC to understand the deployment of SQL scripts using SQL Change Automation Release task in Azure DevOps. In repository I have not checked-in the security folder (which having User & Role creation scripts). The target environment DB where I am deploying, having some set of users to access to that DB. Once deployment completed, User is available. But all the roles are revoked(For example db_reader, db_writer.....).

Is it mandatory to check-in all the security related scripts? Or do we have any option for not clearing those access? 

Kendra_Little

Hi there,

To clarify your setup, which tool are you using to commit changes to your project? Are you using SQL Source Control to do this? Or SQL Change Automation?

Thanks,
Kendra

Kattesang

I am using SQL Source Control to commit my changes to repository.

Kendra_Little

Hello,

Thank you for confirming. With SQL Source Control, I believe that the setting you are looking for is called "Ignore users' permissions and role memberships". You set this on the "Setup" tab under "Comparison Options."

You may also find some additional information useful in this article on Source Controlling Your Database Permissions, which is specific to SQL Source Control. 

Hope this helps,
Kendra