CODEWITHSUNDEEP
sql-serversql-server-2008triggerssql-server-2008-r2database-trigger
Willy
Willy

Reputation: 10660

SQL Server GRANT EXECUTE ON TRIGGER TO USER

I have this trigger:

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

I need to grant EXECUTE permission on this trigger to user SOME_USER so I do:

GRANT EXECUTE
    ON OBJECT::[dbo].[TRIGGER_UPDATE_MODIFIED] TO [SOME_USER]
    WITH GRANT OPTION
    AS [dbo];
GO

And I get following error:

Cannot find object 'TRIGGER_UPDATE_MODIFIED' because it does not exist or user does not have permission.

Why am I getting this error and how can I resolve it?

I have thought about doing this:

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

Is that correct (using WITH EXECUTE AS 'SOME_USER')? or maybe using WITH EXECUTE AS OWNER?

What will be the difference?

Upvotes: 3

Views: 23587

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

To sum up the comments:
You can't grant permissions on DML triggers. You can only grant permissions to execute the DML statements that will fire the trigger. In your case, you should grant update permissions on the table to the user:

GRANT UPDATE ON [dbo].[MY_TABLE] TO [SOME_USER];

Upvotes: 1

Related Questions