title: Best practice on users/roles on SQL Server for a web application
body: <p>I searched online a bit and couldn't find anything that really nailed the spot or covered the bases how to go about setting up users/roles on a database.</p>
  
  <p>Basically, there would be a user that would be used to access the database from the application (web application in this case) that will need access to database for the regular database operations (select, insert, update, delete) and executing stored procedures (with exec to run stored procedures within other stored procedures/UDFs).</p>
  
  <p>Then, we would also have a user that would be main admin (this is simple enough).</p>
  
  <p>I currently have a development environment where we don't really manage the security too well in my opinion (application uses a user with db_owner role, though it is an intranet application). Even though it is an intranet application, we still have security in mind and would like to see what are some of the ways developers set up the users/roles for this type of environment.</p>
  
  <p>EDIT: Web application and SQL Server reside on separate machines.</p>
  
  <p>EDIT: Forgot to mention that an ORM is used that would need direct read/write access.</p>
  
  <p><strong>Question:</strong>
  What are the "best practices" on setting up the user for application access? What roles would apply and what are some of the catches?</p>