Limit Sql data access from Applications using "EXECUTE AS"

When you have to retrieve SQl data from the applications, you would create login and connect using that to the sql server. Any user, who has the permissions to execute the stored procedure, runs the stored procedure under the Database's dbo user (which means it can do anything in the database, but nothing at the server-level nor on other databases). If you only allow your Logins to execute stored procedures (and not touch the tables directly), then you've effectively limited the Logins to code you've written. If you don't write any DELETE statements, then Logins can't delete anything.

With Sql 2005 and above, there is a new feature EXECUTE AS OWNER which is a great way to limit the permissions of a sql server login. Lets look at how this can be used effectively to limit the access.

This feature allows you to impersonate another user in order to validate the necessary permissions that are required to execute the code without having to grant all of the necessary rights to all the underlying objects and commands.

The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a standalone clause to change the user’s context. 

Syntax:

CREATE PROCEDURE.[proc_GetAliasByID]

      @ID bigint = 0

WITH EXECUTE  AS 'AppDMLUser'

More details: http://technet.microsoft.com/en-us/library/ms188354.aspx