Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Execute As Expand / Collapse
Author
Message
Posted Sunday, November 27, 2011 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 8:06 AM
Points: 9, Visits: 404
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!
Post #1212234
Posted Sunday, December 4, 2011 11:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1216007
Posted Friday, October 11, 2013 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 29, 2014 10:48 PM
Points: 2, Visits: 40
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
Post #1503879
Posted Sunday, October 13, 2013 11:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 1,675, Visits: 747
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





Post #1504322
Posted Tuesday, October 15, 2013 10:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:16 AM
Points: 287, Visits: 292
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
Post #1504848
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse