In general, it all depends on the application requirements.

First thing would be to gather the information from the developer who will be supporting that application on things like what exactly the code does.....

That gives us an insight on what permissions they actually need for that application....

Best practice is using a Windows Account and keeping the permissions as minimum as possible.

Usually for most of the APP's: an account with db_datareader, db_datawriter and db_exec roles would suffice.

Again as i said, it all depends on the application code .... 🙂

Hope this helps 🙂