VSTS Build extension - Build a SQL Source Control Project Target CI DB Permissions

vfrank66

I was reading this documentation and trying to figure out the least privileges I can grant the account which runs a VSTS build task "Build a SQL Source Control project". I would like to not use LocalDB and instead provided a temporary server type of "SQL Server". This will only work when the account is in the server role sysadmin. When provided db_owner to the specific database these privileges are insufficient and the error is a failed login. Probably something about cleaning the shadow database and user perm are removed.

I would like to know the least privilegse needed to run "Build a SQL Source Control" project againt a Temporary server type of "SQL Server"

way0utwest

There is a need to create and destroy databases for sure. The "Build" actually wants to run the pre, which does contain a create db, and run all the code to ensure it compiles correctly, as that's the only SQL Server compiler we have.

In terms of using a test instance, we usually go with SA, but I suspect that you could get by with: dbcreator, which hopefully would give you db_owner in the build db and the shadow db. For the release part, you need rights to read the target db.

Note that builds are not usually to a specific name, unless you are sure there aren't collisions with other developers. Since we may want concurrent builds from checkins at similar times, the build task will generate a random name.