Assume I have the stored proc shown below. I have a 'With Execute As'
included. Doesn't this mean that whoever attempts to execute this stored
proc, even if it is executed from a remote server, will get authenticated as
if he were the user were the "sa" user? With all the permissions "sa" has?

ALTER PROCEDURE [dbo].[FederationData_Add] (@ItemType int, @ItemValue
nvarchar(max))
WITH EXECUTE as 'sa'
AS

Insert Into FederationData (ItemType, ItemValue)
    Values (@ItemType, @ItemValue)

No it doesn't. The principal that is using EXECUTE AS must have IMPERSONATE permission on the principal it is trying to execute as. For example for user Joe to do EXECUTE AS USER = 'Jane', an admin would have had to have done something like:

GRANT IMPERSONATE ON USER:Jane to Joe;

Typical user woundn't have that permission.
Dan

A stored procedure WITH EXECUTE AS clause can only specify a database
principal.  'sa' is server level principal so it is not allowed in this
context.

In the case of a database user (e.g. WITH EXECUTE AS 'dbo'), the specified
security context will be used for the duration of the proc.  Execute
permissions on the proc are all that are required when the proc is executed
directly.

Show quoteHide quote

Amos Soma (amos_j_s***@yahoo.com) writes:
To add to Dan Guzman's answer, assuming that the clause reads

   WITH EXECUTE AS 'dbo'

and dbo for a certain database is sa, you may ask will the caller then
has the rights of sa?

The answer is that this depends on the trustworthy setting of the database.
By default this setting is OFF, and in this case any procedure with an
EXECUTE AS clause is sandboxed into the database - and it cannot perform
any operation outside the database where the procedure exists.

However, if the database is set to trustworthy, then all these restrictions
are waived.

The impersonation lasts for the duration of the stored procedure, and
it applies to everything - permissions and what SYSTEM_USER returns. If
the procedure calls another procedure or invokes a trigger or dynamic
SQL, the impersonation lingers.

In short, WITH EXECUTE AS is very powerful, and it is outright dangerous
to put something like WITH EXECUTE AS 'dbo'. Better is to define a
login-less user that is assigned exactly the elevated permissions you
may be looking for in your stored procedure.

I have longer article on my web site that discusses EXECUTE AS together
with other mechanisms to transfer permissions in SQL 2005,
http://www.sommarskog.se/grantperm.html#EXECUTE_AS.

LEFT JOIN... WHERE vs AND ?
Optimizing this crazy setup...
how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric