Currently, our deverloper group has started using DbUp and Octopus to do Continuous Integration on SQL Database Deployments. We have created a Domain user which will utilized to do the SQL Database deployment via Octopus server.
I wanted to know, what permissions should a Domain user should be granted for Continuous Integration process?
I don't want to give "SysAdmin" permission to this Domain user.
Do people have implemented above combination for there Continuous Integration of DB Deployment?
Best Answer
Assuming you are using SQL Server 2008 and up, A better way of doing is to create a role in the database and grant that role permissions.
You can add users to the role, so they will inherit the permissions of the role.
-- to grant CREATE, ALTER, DROP OBJECTS (tables, procs, functions, views) with ALTER permissions on the schema. You can obviously fine tune below ones as per your needs.
-- Add an existing user to the new role created