I'm working on an ASP.NET MVC 3 project which uses One-Click Deploy, with Web Deploy running on the development server I'm deploying to. The project includes a SQL Server database. I have it set up in the project properties, completely with a deployment-only connection string (in Connection string for destination database) with a SQL Server login (call it BuildGuy) that has dbcreator rights.
I'm also using a build server (Jenkins). I have it set up to use the command line to build my project, run tests, create a deployment package, and then deploy to our shared development server.
All of this works from the build server except the SQL Server deployment piece. I'm using ScriptDropsFirst in Presource in my project to drop the tables, then re-create them. When I do the one-click deployment from Visual Studio 2010, it works fine. When the build server deploys from the package, the SQL Server piece fails with this message: "The user does not have permission to perform this action." The SQL Server deploy only succeeds from the build server if the login, BuildGuy, has the sysadmin role.
Meanwhile, I tried removing both sysadmin and dbcreator from BuildGuy and found that the deploy still works when I do it from Visual Studio 2010. It appears that Visual Studio 2010 is using *my* creden
View Complete Post