Where to set permissions to all server for logon trigger on sql server 2005

I need to keep track of the last login time for each user in our SQL Server 2005 database.

I created a trigger like this:

CREATE TRIGGER LogonTimeStamp
ON ALL SERVER FOR LOGON
AS
BEGIN
  IF EXISTS (SELECT * FROM miscdb..user_last_login WHERE user_id = SYSTEM_USER)
    UPDATE miscdb..user_last_login SET last_login = GETDATE() WHERE user_id = SYSTEM_USER
  ELSE
    INSERT 开发者_如何学编程INTO miscdb..user_last_login (user_id,last_login) VALUES (SYSTEM_USER,GETDATE())
END;
go

This trigger works for users that are system admins but it won't allow regular users to login. I have granted public select,insert and update to the table but that doesn't seem to be the issue. Is there a way to set permissions on the trigger? Is there something else I am missing?

Regular users get the error message:

Logon failed for login 'xxxx' due to trigger execution. 
Changed database context to 'xxxx', 
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892).

Thanks

EDIT: We have just noticed that this only happens for active directory accounts, not sql accounts.

For example:

C:\>osql -S server -E
Logon failed for login 'xxx\xxxx' due to trigger execution.

C:\>osql -S server -U xxxx
Password:
1>

First of all, I would strongly recommend that you move this table to either [master] or [msdb]. Having a Logon trigger dependent on any non-system database is very problematic.

Secondly, if the current question is "How to grant rights to all logins?" as stated in your comment, then the answer is: "Grant them to the Public server role." Every login is in that role, so its permissions apply to everyone.

CREATE TRIGGER [Logon_Audit_Tgr] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN ....