The principle of least privilege should apply everywhere, certainly in production, but also in development. If you limit permissions in development, you might cause a few headaches, but you’ll understand the issues and solve them early on. More importantly, if you have security flaws, they aren’t in production systems where data is exposed.
SQL Server security isn’t that hard, but it can be cumbersome. Set it up properly in development, keep your scripts (even from the GUI), and then use those scripts for your production systems.
Red Gate’s usually pretty good about publishing minimum permission requirements; some vendors will simply say “you need sysadmin or db_owner.” I’m not enthralled with vendors which take the lazy way out.