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

Posted by Jay on Server Fault See other posts from Server Fault or by Jay
Published on 2011-01-11T20:15:35Z Indexed on 2011/01/11 20:56 UTC
Read the original article Hit count: 231

Filed under:
|

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 servers 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?

Thanks

© Server Fault or respective owner

Related posts about sql-server

Related posts about sql-server-2005