SQL Server Security with EXECUTE AS OWNER

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.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in SQL Server Development. Bookmark the permalink.

51 Responses to SQL Server Security with EXECUTE AS OWNER

  1. How to Register for a Userbolavip Account

    To be able to register as a part upon the Userbolavip internet site, the method is
    very easy, typically the first step a person have to do is visit the official website or
    even through the most recent alternative link provided by Userbolavip: Bukauserbolavip.
    com

    Via the alternative link above you will go directly to the
    key page as well as in the upper right corner, click
    on the ‘REGISTER’ button to get into the registration webpage.

    Then you will locate a page along with several fields that will must be filled in with your personal data completely in addition to correctly.

    After generating sure that just about all fields are packed in correctly plus correctly, complete the particular registration process by simply
    pressing the ‘REGISTER’ button which can be at the bottom associated with the registration webpage.

    Within a few minutes you will acquire confirmation the bank account you created is active.

    Userbolavip Game Login

    After registering, you will acquire a person ID plus password which a person can use
    in order to log into the game. To do so, return to the primary webpage from
    the userbolavip internet site including the leading you will discover two
    columns that the username and password.

    Enter your username and security password
    in both career fields and press the particular sign in switch close to
    them. Once successful, you may be able to access internet gambling video games provided by Userbolavip.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>