WITH EXECUTE AS in Stored Procedure

Question

WITH EXECUTE AS in Stored Procedure

richarddelcarmen

SSChasing Mays

Points: 625
More actions
June 9, 2011 at 1:40 am

#241048

Hi guys,
I cannot make the EXECUTE AS 'user_name' work in the stored procedure.
I want to force the execution of a stored procedure to be in the security context of a certain login. I did this by using the WITH EXECUTE AS 'user_name' clause. This way the permissions of the objects in the procedure will be validated against the 'user_name' in the EXECUTE AS clause.
Here is the sample stored procedure.
CREATE PROCEDURE proc1
WITH EXECUTE AS 'user1'
AS
BEGIN
SELECT * FROM table1
END
Then I call this stored procedure using 'user2' login that have EXECUTE permission on the stored procedure above and IMPERSONATE permission on 'user1'. Everything works well and 'proc1' gets executed.
But if I deny SELECT permission on 'table1' to 'user1', the stored procedure still gets executed and data is returned. This should not happen since the security context is with 'user1' who does not have the permission to SELECT data.
I even tried denying SELECT permission on 'table1' to 'user2' and it still works.
Here is the link about the EXECUTE AS: http://msdn.microsoft.com/en-us/library/ms188354.aspx
Hope you can shed some light on this.
Thank you very much.

Viewing 5 posts - 1 through 4 (of 4 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

what type of permission for user2. But be the case the case that he have some high level permission which inherit these base level permission automatically.

----------
Ashish

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 2

I suspect the IMPERSONATE permission may be the issue. You have already given access to everything user1 can access.

Try revoking the IMPERSONATE permission.

The whole idea of EXECUTE AS is to enable temporary elevation of permissions and automatically revoke that permission in the isolated environment of a stored procedure or query.

richarddelcarmen SSChasing Mays Points: 625 More actions · Answer 3

Hi Ashish,

'user2' only has public server role on the database and EXECUTE permission on 'proc1'.

Hi jerry-621596,

The IMPERSONATE permission of 'user2' to 'user1' is required when using the EXECUTE AS clause.

I already given up to this approach. As an alternative, I enveloped that execution of 'proc1' with EXECUTE AS USER. Although the above approach is more secured since the security context is enforced at the procedure level.

The workaround:

EXECUTE AS USER = 'user1'

GO

proc1

REVERT

Thanks everyone. Hope we can make the original approach work.

Don Halloran SSCertifiable Points: 5937 More actions · Answer 4

You only need explicit impersonate permissions if you're using it as a standalone statement (ie, not as part of a module declaration), or (IIRC) if you're leaving the context of the database.