What SQL permissions do an application really need in order to work? The requirements will be based on the functions exposed by an application.
If you have an off the shelf application that is self-contained, it may require permissions for database backup and restore actions. Bulk Copy for import and export is often included. If your application allows users to define their own data structures, you may need to allow table, view or function creation.
If your application is for internal purposes, it is often easier than working with a canned product. The reason is that you generally have support staff handling things such as backup, and database extension.
Regardless of your application type, it is a good practice to restrict what a client can do when connected to your database. Schemas make a lot of sense. You can isolate your core application tables and logic from that defined by users. You have the option of access all data through stored procedures, while not granting any access to a table or view directly. You can restrict data access through views. I you can create schema groups that grant permissions based on a role, you can grant a user permissions to a schema, instead of having to assign individual permissions to each individual object.
A key practice to secure your database and block it from intrusion is to not only deny application access through an administrator account, such as SA, but to also to not allow your application to be a member of the DBO (database owner) role. Different roles are already built into SQL Server, and allow the connection to perform different kinds of tasks.A good example is the use of a different login and role for tasks such as backup and restore, and restrict the appropriate users to those roles.
Share a comment with other practices you find essential to protect your database from improper use through holes in your application permissions.
Cheers,
Ben