What are your Production Database Permissions?

We use SQL Change Automation to deploy database changes via a TFS pipeline. The development team control the deployment up to the Test environments and then there is an approval step (need to raise a Change Record) prior to the Production deployment (which includes running a database backup). The key point here is that with the automation we've built, the process doesn't need the Prod DBAs to be involved. We've got tables, views, SPs etc under source control as well as database roles and their permissions (users are excluded and the Prod DBAs will execute the scripts to create users and add them to roles when required). The Production deployment is executed with a Production TFS queue which runs under an AD account which is a user in the Production database. After many discussions with our Prod DBAs and following the principal of least privileges required, the account is a member of the db_ddladmin role. If we require any additional permissions to execute the deployment (e.g. to add permissions to a role) we have to specifically request them to be added to the AD account by the Prod DBAs for the duration of the deployment - this obviously takes away automation, our independence of running releases and adds additional manual steps to the process (we need to raise a request for their time, write and test the scripts for the additional permissions and write a handover document).

So, I'm interested in hearing other people's experience of the permissions they have on Production databases when deploying changes and views on how to approach automated deployments. 



Sign In or Register to comment.