SQL User Permissions for Continuous Integration via DbUp and Octopus

Question

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?

Answer 1

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.

USE db_name;
 CREATE ROLE [new_role] AUTHORIZATION [dbo];
grant alter
      ,delete
      ,execute
      ,insert
      ,references
      ,select
      ,update
      ,view definition
on schema::dbo
to new_role;

grant create table
     ,create procedure
     ,create function
     ,create view
to new_role;

-- Add an existing user to the new role created

  EXEC sp_addrolemember 'new_role', 'DBUserName'
  GO