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.

56 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.

  2. Most Trusted Online Gambling Site in Indonesia in 2021

    Nowadays playing online gambling is no stranger to the Indonesian people.
    Because most of these games are played by adults to parents.

    Playing online gambling is agreed simple nowadays. Especially afterward the presence of increasingly higher smartphones, the presence of various
    leading online gambling sites in Indonesia is growing.
    One of the newest and most trusted online gambling sites at this become old is Sakura188 agent.

    Here you will locate various privileges in the manner of you achievement on this site.
    The reason is this site offers various advantages in its services.
    In serving enlargement payments Sakura 188 agents themselves have various payment methods such as payment via bank transfer or via further
    payment merchants such as via ovo, linkaja, fund or gopay.
    This payment method is fairly practical and can create it simple for players if they want to do
    something at Sakura188.

    In addition, at Agent Sakura 188 there is an online gambling
    game that is unconditionally popular and can be played via smartphone or PC.
    The games in Sakura188 have various types. for
    that reason that this website has lots of renowned and
    easy-to-win games for any player. for that reason it’s not wrong if this online gambling
    site is arguably extremely trusted. Even while it’s new,
    the number of online gambling games that you can play is next extremely diverse.
    Here are several kinds of games that you can undertaking upon the sakura188 site.

  3. Michele says:

    Depobola
    Are you a fan of internet gambling games? Here we suggest not to choose a
    soccer agent carelessly, because these days there are
    many online soccer brokers who impersonate recognized agents to ask you to generate income
    from you.

    Depobola is definitely an online soccer agent
    that has passed the test from World gambling experts, and already has an official permit obtained from an official internet gambling company
    in Asia, besides that Depobola is one of the agents
    that provides complete products and products
    that already have official permit from the world gambling company.

    Depobola is also one of the sites that delivers the
    best service for you, here we provide all services
    from professional customer support who will serve you, our top
    concern is the security of our members’ personal data privacy.

    This is because the Depobola site is one of the recommended sites from online gambling centers as a trusted online wagering site.

    How to Register Depobola
    Here Depobola provides you with instructions
    for how to register on the Depobola website, here there are only 3 steps for you to claim your IDENTITY at
    Depobola.

  4. スーパーコピーブランド専門ネットショップで、
    ファッション時計,バッグ,財布とその他の世界一流ブランドスーパーコピー激安販売。
    弊店のスーパー コピーブランド時計は日本全国送料手数料無料です。
    スーパーコピー品の華麗さや実用でお客様に驚喜を与えます。
    サービスが素早くて親切で商品もきれいでとても気に入りました。永遠がありましたら、また宜しくお願いします。どうもありがとうございました。^o^★PRADA プラダ★トートバッグ★BR2167★ナイロン×カーフ★ベージュ×ブラウン★ https://www.gooshop001.com/product/detail/1089.htm

  5. ブラントブランドコピー品
    マストな新作アイテム続々入荷中…
    長年の豊富な経験と実績を持ち、
    ブラントブランドコピー品の完壁な品質を維持するために、
    一流の素材を選択し、精巧な作り方でまるで本物のようなな製品を造ります。
    高品質の商品を低価格で提供する、納期を厳守することは弊社の経営理念です。
    今、マストなブランドコピー新作アイテム続々入荷中…
    【シャネルコピー、ヴィトンコピー、コピーグッチ、エルメスコピー】ブランド財布コピー、バッグコピー腕時計コピーぜひおすすめです。
    スーパーコピーブランド通販 ブランドスーパーコピー格安買取価値あるスーパーコピー財布、バッグのルイヴィトンコピー販売専門店日本最大級スーパーコピー、通販、ヴィトンコピー、代引き、レプリカ、財布激安、卸売り、偽物、シャネル財布、バッグ、時計、代金引換、スーパーコピー専門店販売送料無料。ホームページ上でのご注文は24時間受け付けております https://www.bagb78.com/goods-6024.html

  6. 発送までスマート且つ迅速な対応で、メール内容も丁寧でした。
    梱包に至ってはビックリするほど非常に丁寧で、嬉しい配慮です。
    商品についても安心して購入できるお店と云う印象もあり、
    また好みの商品があれば、こちらを是非利用したいと思っています。
    SALEが開催中◆ブラントコピー人気通販店の大SALEが開催中◆弊社は長年の豊富な経験と実績を持ち、ブラントスーパーコピー品の完壁な品質を維持するために、一流の素材を選択し、精巧な作り方でまるで本物のようなな製品を造ります。また、お客様のご注文商品を責任を持ってお届けいたします。 https://www.tentenok.com/product-3668.html

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>