EXECUTE AS OWNER is a great way to limit the permissions of a SQL Server Login. The general idea is to create your stored procedure with the EXECUTE AS OWNER modifier. 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.
This is better than Roles, because Roles are very coarse in comparison. With Roles, you may have to give a User INSERT permissions on table. Instead with EXECUTE AS OWNER you can write a stored procedure that checks the data exactly the way you want in the body of the stored procedure. This is much more fine grained way of handling permissions.
From beginning to end, this is what you do:
Create a Login:
CREATE LOGIN [MyLogin] WITH PASSWORD=N'Password',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
Create its User in the database:
CREATE USER [MyUser] FOR LOGIN [MyLogin];
I prefer to use schemas to identify "public" stored procedures. So create a schema:
CREATE SCHEMA [public] AUTHORIZATION [dbo];
Give your new user EXECUTE permissions on anything in the public schema: (We will put the new stored procedure in this schema):
GRANT EXECUTE ON SCHEMA::[public] TO [MyUser];
Create your stored procedure:
CREATE PROCEDURE [public].[MyStoredProc]
(
@Param1 int
)
WITH EXECUTE AS OWNER -- This "EXECUTE AS" modifier on the stored procedure is key!
AS
BEGIN
SET NOCOUNT ON;
-- do something
END
When your stored procedure runs, it can do anything in the database, including calling other stored procedures. It is an easy way to segregate public stored procedures from private ones. This gives you encapsulation, which is a good thing (see section 5.3 in Code Complete about the benefits of encapsulation).
The only permissions outside users need is EXECUTE permission on the public schema, so it is easy to add new stored procedures by creating them in the public schema.
Instead of Roles, you can have schemas. Let's say you would have 3 roles in the database: admin, anon, and general. The admin role is for Logins that perform administrative activity on a website. The anon role is for people who view your site anonymously, and the general role is for stored procedures that are for both. You can instead, with EXECUTE AS OWNER, create three schemas for your stored procedures: admin, anon, and general. If you want the stored procedure to have admin only Logins to use it, create the stored procedure in the admin schema. The same goes for the other schemas.
