Execute As

  • Comments posted to this topic are about the item Execute As

  • Hello I noticed in the article that you say the test database will be called MyTesting, however the script creates it as TestDB. Just thought I'd point this out. Otherwise, good article from what I can tell without actually running the code.

    Cheers,

    Charley

  • I haven't taken the time to run the code yet either... but I did read the article. The title of the article doesn't even come close to describing the great features used in this article. I realize that EXECUTE AS is the main purpose of the article but this article also does a very, very nice job of providing an introduction to one important type of security. I'd call the article something like "Restricting Access Using EXECUTE AS".

    If the code in this article turns out to be as good as the article itself, I'll be making a permanent link to this one. It's short, sweet, and very much to the point.

    Well done, Patrick.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, thank you for the kind words. This is my first article, and I found out that coming up with a good title is more difficult than it seems.

  • I think that the following line of code only works on SQL 2008

    DECLARE @i INT = 10

    In SQL 2005, it should be

    DECLARE @i INT

    SET @i = 10

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

  • You're right the one can't declare and assign in the same line with local variables in SQL 2005. Adding that feature in SQL 2008 made turning ad-hoc versions of stored procedures sooo much easier.

  • Thank you for your article, Patrick.

    Not only does it cover the implementation of EXECUTE AS but it also demonstrates a very interesting example of certificates and builtin criptography. Much appreciated contribution. 🙂

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • Patrick Cahill (2/2/2011)


    This is my first article...

    Then my hat is really off to you, Patrick. Comprehesive code with a decent format embedded in a well written article that explains everything along the way... you just can't ask for much better on your maiden voyage as an author. Well done, again!

    ...and I found out that coming up with a good title is more difficult than it seems.

    Heh... you noticed that? Even a well written fellow like Steve Jones runs into that problem. However, if that's the worst problem with one of your articles, it's not really a problem

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • charles evans (2/2/2011)


    You're right the one can't declare and assign in the same line with local variables in SQL 2005. Adding that feature in SQL 2008 made turning ad-hoc versions of stored procedures sooo much easier.

    All MS needs to do now is figure out that column and variable selection along with variable assignment in the same SELECT (kind of like they do in update) would be incredibly useful and my day would be made! 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the article, very helpful on many levels. This is the most straightforward and concise discussion of execute as and encryption that I've encountered. Great article.

  • Hi, I follow the steps on 2008R2. When I execute

    EXECUTE AS LOGIN = 'ApplicationUser'

    I got error:

    Cannot execute as the server principal because the principal "ApplicationUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    Any ideas? Thanks!

  • califny (11/27/2011)


    Hi, I follow the steps on 2008R2. When I execute

    EXECUTE AS LOGIN = 'ApplicationUser'

    I got error:

    Cannot execute as the server principal because the principal "ApplicationUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    Any ideas? Thanks!

    Do you have a login called "ApplicationUser"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • First of all, congratulations. I'd always wanted to check out encryption and that article made my day! 🙂

    I know a little bit (a dangerous thing, I know!) about execute as, especially the joy of switching context across databases, but I digress... Anyway, just curious about your reasons for using impersonation (in the context of your example).

    >By using the "execute as" I am better able to control which users have access to the encrypted data

    If I follow your example correctly, anyone with execute rights on getDecryptionwithExecute gets the EncryptionUser permissions and hence full access to the decrypted data...

    I would have thought that the approach might be something like:

    Grant execute to getDecryption to EncryptionUser

    Deny execute on object::getDecryption to [MyUsers]

    then after that the only way to access the encrypted data would be to impersonate EncryptionUser

    execute as EncryptionUser

    exec getDecryption

    revert

  • Great article - well laid out and explains this clearly -- and works a peach on SQL Server 2014.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Thanks for the article. I use WITH EXECUTE AS quite a bit in our environment but gained an appreciation for using it to secure encrypted resources.

    I do have one question, however. The application user is only granted select and execute permissions to the schema. What is it about permissions / ownership chaining that allows the application user to execute dbo.getEncryptionWithExecute WITH EXECUTE AS 'EncryptionUser' without knowing the password for the encryption user? Can I presume it's because all the objects are owned by dbo? Any security considerations that I should be aware of?

    Thanks,

    Andre

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply