1

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?

  • Added sql-server tag based on the mentioning of sqlcmd – a_horse_with_no_name Apr 14 '15 at 17:57
2

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
  • Thank you Colin 't Hart for adding comment and Thank you Kin for your response. Yes, we are using SQL 2008, 2008 R2, 2012 in our environment – NismoGTR05 Apr 14 '15 at 19:13
  • I unchecked the answer, as I wanted to get more information on, what are the other approach people are using to setup a user, which can be utilized under Continuous Integration for the database deployment. – NismoGTR05 Aug 5 '15 at 15:55
  • @NismoGTR05 I completely agree with you, but I believe that is what the upvotes are for - if an answer is helpful, upvote it. Its not necessary that an accepted answer will be the best answer. There are cases where other answers are having more upvotes than the accepted answer. I am curious what other approaches are you looking for ? – Kin Shah Aug 5 '15 at 16:23
  • I am curious to know, if anybody implemented auto DB deployment and try to avoid failure security wise during the deployment – NismoGTR05 Aug 6 '15 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.