Security - Do you configure your web applications to use specific accounts for database access?

Do you configure your web applications to use application specific accounts for database access?

An application's database access profile should be as restricted as possible, so that in the case that it is compromised, the damage will be limited.

Application database access should be also be restricted to only the application's database, and none of the other databases on the server

Most web applications need full read and write access to one database. In the case of EF Code first migrations, they might also need DDL admin rights. These roles are built in database roles:

db_ddladmin	Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter	Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader	Members of the db_datareader fixed database role can read all data from all user tables.

Table: Database roles taken from Database-Level Roles

If you are running a web application on Azure as you should configure you application to use its own specific account that has some restrictions. The following script demonstrates setting up an sql user for myappstaging and another for myappproduction that also use EF code first migrations:

USE master

GO

CREATE LOGIN myappstaging WITH PASSWORD = '************';
GO
CREATE USER myappstaging FROM LOGIN myappstaging;
GO

USE myapp-staging-db;
GO

CREATE USER myappstaging FROM LOGIN myappstaging;
GO

EXEC sp_addrolemember 'db_datareader', myappstaging;
EXEC sp_addrolemember 'db_datawriter', myappstaging;
EXEC sp_addrolemember 'db_ddladmin', myappstaging;

Figure: Example script to create a service user for myappstaging

Note: If you are using stored procedures, you will also need to grant execute permissions to the user. E.g.

GRANT EXECUTE TO myappstaging

Figure: Example connection string