4

This question is about impersonation in Microsoft SQL Server. I want to know that if I have impersonation permissions on a specific login then I can easily impersonate that login whenever I want and I do not need to have a stored procedure with EXECUTE AS OWNER? If that is so then what is the purpose of a stored procedure with EXECUTE AS?

3

Excellent question. Yes, if LoginA (or UserA) is granted IMPERSONATE on LoginB (or UserB), then LoginA (or UserA) can execute the EXECUTE AS statement whenever they want, without restriction:

EXECUTE AS LOGIN = 'LoginB';

or

EXECUTE AS USER = 'UserB';

 

then what is the purpose of a stored procedure with EXECUTE AS?

The purpose of the EXECUTE AS clause (part of the CREATE {module} statement) is to temporarily grant that IMPERSONATE permission only in the context of that module. This prevents you from needing to explicitly grant IMPERSONATE. The difference is, outside of the context of that module with EXECUTE AS {SomeUser}, whoever is executing that module shouldn't have the ability to impersonate that User. You don't want Logins / Users being able to impersonate others whenever they want. That is a security risk. The EXECUTE AS clause gives you the ability to let a principal impersonate another only when you want, not when they try to on their own (where you can't restrict what they do with those elevated permissions). So, you actually do want the EXECUTE AS clause, and do not want to grant IMPERSONATE to anyone ever (if it can be avoided, and most likely it can be).

For example: There is no TRUNCATE TABLE permission. If you want someone to be able to truncate a particular table (that they do not own, since they do have permission to do that) you could grant them IMPERSONATE on dbo. However, there is then no way to restrict their actions to only issuing TRUNCATE TABLE, and only on that particular table. That User can execute EXECUTE AS USER = 'dbo'; whenever they want, and then do whatever they want. However, by adding EXECUTE AS 'dbo' to the CREATE PROCEDURE .... AS TRUNCATE TABLE {TableName}; statement, whoever executes that module will still impersonate dbo, but only for the actions in that module, which here is just truncating a particular table.

Of course, the EXECUTE AS clause of a CREATE statement can only impersonate another User (database-level only). It cannot impersonate another Login (instance-level). This limitation is what leads too many people to enable TRUSTWORTHY so that a Login matching the same SID of the User being impersonated will be able to temporarily grant additional permissions. But this is quite dangerous, which is why the ideal (and far better) mechanism for temporarily elevating permissions is Module Signing.

For more details on why impersonation, as well as TRUSTWORTHY ON and Cross-DB Ownership Chaining, are bad, and how / why Module Signing is better, please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

  • Excellent Answer, Although my questions was not very clear but you did make everything crystal clear. Thank you so much. Please correct me if I am wrong what I understood from your explanation. So basically EXECUTE AS and EXECUTE AS CLAUSE can be called two separate things. To be able to use EXECUTE AS, one must have IMPERSONATE permissions that gives it the ability to use it when ever it want while a module specified with EXECUTE AS CLAUSE does not necessarily need IMPERSONATE permissions but is limited to the database level. Thanks again, I am going to go through the material you advised. – Zeshan Mujtaba May 2 at 19:20
  • @ZeshanMujtaba Just to be clear, the word "CLAUSE" is not part of it. That just indicates the difference between the clause and the statement. I have updated my answer to hopefully be clearer about this, including a new example paragraph to illustrate the difference. And the first mention of each is now a link to the documentation. Regarding your understanding of this info: mostly yes. But remove the word "necessarily" since the clause does not need IMPERSONATE permission. Also, it is limited to Users, which are database, but can link to logins if TRUSTWORTHY is ON, so don't do that ;-). – Solomon Rutzky May 2 at 21:37
  • Perfect. Thanks again. Yes you are right CLAUSE is not part of the syntax. If EXECUTE AS is specified in CREATE statement of a module (Stored Procedure) then it can be called a CLAUSE, otherwise on its own its a STATEMENT. This is exactly what is really confusing because it seems like that they are the same thing but using the EXECUTE AS in the CREATE statement of a stored procedure changes things a bit. Thanks again Solomon, now that things are a lot clearer, I am not going to turn the TRUSTWORTHY to ON :) – Zeshan Mujtaba May 3 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.