Where to set permissions to all server for logon trigger on sql server 2005
- by Jay
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
