These days, developers and database administrators have to pay more attention to database and application security than ever before. Not long ago, even major applications ran using the sysadmin account. We have made much progress in terms of general awareness of security issues, but there are still plenty of applications out there that run with excessive privileges.
The best database application security advice I can give is that whenever possible, you should allow data access through stored procedures only. When you use that approach, the login used by the application won't need to be granted any read or write privileges on the database tables called by stored procedures, as long as the schema that owns them is the same. Instead, grant EXECUTE permission on all stored procedures, or just on a subset related to the login used by the application. Unfortunately, this practice is not widely followed. Moreover, object relational mapping (ORM) tools have recently become more popular and more widely used than in the past, and these tools often generate and execute data updates directly against tables, rather than using stored procedures.
In older versions of SQL Server (prior to 2005), DBAs used a script that would generate another script to loop through all of the stored procedures in a database and assign EXECUTE permission to a login. But, starting with SQL Server 2005, you can easily grant EXECUTE permission on all stored procedures in a database. There is a technique I recommend for the sake of clarity and consistency, and it consists of creating a database-level role called db_executor. We grant the EXECUTE permission to the db_executor role and then make a particular database user a member of that role. This is very consistent with the way Microsoft created db_reader, db_writer and other roles, making it easy to assign certain permissions to a database user at the database level.
Here is a script you will need to execute in the database you set up:
-- create custom database role
CREATE ROLE db_executor
-- grant EXECUTE permission
GRANT EXECUTE TO db_executor
-- add security account to the role
exec sp_addrolemember 'db_executor', 'MyAppUser'
After executing the above script, the MyAppUser database user will be able to execute all stored procedures (with some exceptions), and it should be sufficient enough to run most typical database applications. Some exceptions exist, mainly in cases that require higher level privileges. For example, the TRUNCATE TABLE command requires at least ALTER privileges on a table, and that is not something you typically want to grant an application account. In that case, a workaround is to set up stored procedures that execute the TRUNCATE TABLE command in the context of the stored procedure's owner:
CREATE PROCEDURE [dbo].[TruncatePeopleTable]
WITH EXECUTE AS OWNER
TRUNCATE TABLE dbo.People
This procedure would typically fail if the user only had EXECUTE permission on the procedure and no other permissions in the database, but since the procedure is created with the "WITH EXECUTE AS OWNER" option, the privileges at runtime get escalated to the owner of the procedure. In this case, the account is dbo (database owner), and since that account has permissions to execute TRUNCATE TABLE, anybody that has EXECUTE privileges on this procedure will be able to truncate tables.
But, as I mentioned, we do not live in an ideal world and many applications will need to update tables directly. The easiest way to do it is at the database level, granting both READ and WRITE permissions to a particular account. You can do this in the SQL Server Management Studio UI tools or you can execute the following commands:
>EXEC sp_addrolemember 'db_datawriter', 'MyAppUser'
EXEC sp_addrolemember 'db_datareader', 'MyAppUser'
These two database-level roles, db_datareader and db_datawriter, grant privileges at the database level so that these permissions will work on all tables. Although it's not done very often, the best security practice recommendation would be to use this approach when the application account really needs to use all tables. If you want to restrict what the application can do in the database, you can grant READ/WRITE privileges only on the tables that are needed and nothing else.
In highly sensitive applications, you could actually go granular and grant permissions only on selected columns on a given table. If the account needs access to a vast majority of tables and you only want to restrict access on a few tables, you could deny read or write permissions on these tables to an account that otherwise would be able to read and write through db_datareader and db_datawriter role membership:
DENY DELETE ON [dbo].[People] TO [MyAppUser]
DENY INSERT ON [dbo].[People] TO [MyAppUser]
DENY SELECT ON [dbo].[People] TO [MyAppUser]
DENY UPDATE ON [dbo].[People] TO [MyAppUser]
The above script would restrict any read or write permissions for the MyAppUser database user. However, keep in mind that the user would still be able to modify the table through stored procedures. So in order to fully restrict access to the table, you would have to deny EXECUTE permissions on these procedures, which would look something like this:
DENY EXECUTE ON [dbo].[InsertPerson] TO [MyAppUser]