title: SQL Server GRANT EXECUTE ON TRIGGER TO USER
body: <p>I have this trigger:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TRIGGER_UPDATE_MODIFIED]
     ON  [dbo].[MY_TABLE]   
     AFTER UPDATE 
  AS 
  BEGIN
  
      SET NOCOUNT ON;    
  
      UPDATE [dbo].[MY_TABLE] SET [DATE_MODIFIED] = GETDATE(), [USER_MODIFIED] = CASE WHEN UPDATE ([USER_MODIFIED]) THEN i.[USER_MODIFIED] ELSE RIGHT(SUSER_NAME(), 100) END
      FROM INSERTED i
      WHERE [MY_TABLE].[SOME_FIELD] = i.[SOME_FIELD]
  
  END
  </code></pre>
  
  <p>I need to grant <code>EXECUTE</code> permission on this trigger to user <code>SOME_USER</code> so I do:</p>
  
  <pre><code>GRANT EXECUTE
      ON OBJECT::[dbo].[TRIGGER_UPDATE_MODIFIED] TO [SOME_USER]
      WITH GRANT OPTION
      AS [dbo];
  GO
  </code></pre>
  
  <p>And I get following error:</p>
  
  <blockquote>
    <p>Cannot find object 'TRIGGER_UPDATE_MODIFIED' because it does not exist or user does not have permission.</p>
  </blockquote>
  
  <p>Why am I getting this error and how can I resolve it?</p>
  
  <p>I have thought about doing this:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[TRIGGER_UPDATE_MODIFIED]
     ON  [dbo].[MY_TABLE]   
     WITH EXECUTE AS 'SOME_USER'
     AFTER UPDATE 
  AS 
  BEGIN
  
      SET NOCOUNT ON;    
  
      UPDATE [dbo].[MY_TABLE] SET [DATE_MODIFIED] = GETDATE(), [USER_MODIFIED] = CASE WHEN UPDATE ([USER_MODIFIED]) THEN i.[USER_MODIFIED] ELSE RIGHT(SUSER_NAME(), 100) END
      FROM INSERTED i
      WHERE [MY_TABLE].[SOME_FIELD] = i.[SOME_FIELD]
  
  END
  </code></pre>
  
  <p>Is that correct (using <code>WITH EXECUTE AS 'SOME_USER'</code>)? or maybe using <code>WITH EXECUTE AS OWNER</code>?</p>
  
  <p>What will be the difference?</p>