Issue with EXECUTE AS SELF

• sar1372

  SSC Enthusiast

  Points: 141

  More actions

  January 25, 2012 at 9:44 am

  #391404

  I'm getting a weird behavior that has to do with EXECUTE AS SELF on a stored procedure.

  If i run the following i get a list of all of the processes on the SQL Server.

  SELECT*

  FROMmaster..sysprocesses

  Then i create a stored procedure like so:

  CREATE PROCEDURE sysprocesses_test

  WITH EXECUTE AS SELF

  AS

  BEGIN

  SELECT*

  FROMmaster..sysprocesses

  END

  When i execute the procedure it also returns all of the current processes.

  But now this is where things get weird and i need help. I took a backup of the database and restored it to the same server. This time when i execute the procedure i only see my current spid, not all of them. However if i run the SELECT from sysprocesses i still see all of the processes.

  I tried to Alter the procedure and even dropped it and created it again, but I still get the same behavior. I've looked at all of the database logins and roles but everything seems the same as the database that this works on. When i look at the procedure properties on both databases i see EXECUTE AS dbo. If i remove EXECUTE AS SELF from the procedure then all processes are retrieved.

  The login that I'm using a sysadmin on the sql server instance.

  Thanks!

• umasingh

  Ten Centuries

  Points: 1182

  More actions

  January 25, 2012 at 10:48 am

  #1438322

  This will resolve after Setting database option "TRUSTWORTHY" on

  ALTER DATABASE <database_name> SET TRUSTWORTHY ON

• sar1372

  SSC Enthusiast

  Points: 141

  More actions

  January 25, 2012 at 11:34 am

  #1438342

  That worked.

  Thanks!