Execute As

Question

Execute As

Patrick Cahill

SSCertifiable

Points: 6470
More actions
February 1, 2011 at 8:54 pm

#234380

Comments posted to this topic are about the item Execute As

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

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

charles evans SSCommitted Points: 1865 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

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)

Patrick Cahill SSCertifiable Points: 6470 More actions · Answer 3

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.

BCC-493036 Mr or Mrs. 500 Points: 555 More actions · Answer 4

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:

charles evans SSCommitted Points: 1865 More actions · Answer 5

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.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 8

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)

gholden Grasshopper Points: 15 More actions · Answer 9

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.

califny Old Hand Points: 378 More actions · Answer 10

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!

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 11

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)

Ununoctium SSC Enthusiast Points: 102 More actions · Answer 12

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 13

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

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 14

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