Ack, good catch 😀

The reason I used master was that I didn't want to risk the database or table not being there, but I think I'm going to just check for that in the trigger and have it abort if anything is missing. I think I will put the tables in another database...

Right now I'm toying with having a table that stores a list of applications to check against, and another table for users who are allowed to bypass the check. Basically the situation we have is that most of our users have read access because of the way our applications work; but a few of them have been abusing it via Excel. We want to be able to block the general population from doing this, but there are a few people we need to allow.

So far I have:

create TRIGGER [tr_check_logins]

ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON

AS

BEGIN

declare @app varchar(200) = left(APP_NAME(), 200),

@user varchar(60) = left(suser_sname(), 60)

/* if table does not exist, abort the trigger */

if object_id('master.dbo.CheckLogins') is null

begin

return;

end

/* if table does not exist, abort the trigger */

if object_id('master.dbo.CheckedApplications') is null

begin

return;

end

/* if table does not exist, abort the trigger */

if object_id('master.dbo.CheckedApplicationUsers') is null

begin

return;

end

/* if the application name matches a name in the table... */

if exists (

select * from CheckedApplications (nolock) where @app like '%'+ApplicationName+'%'

)

begin

if not exists (

select * from CheckedApplicationUsers (nolock) where @user = UserName

)

begin /* user is not allowed to bypass */

ROLLBACK /* comment out if we want to allow all Office connections */

insert master.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn)

values (left(SUSER_NAME(), 60), left(APP_NAME(), 200), getdate(), 'No')

return;

end

else

begin /* user is allowed to bypass */

insert master.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn)

values (left(SUSER_NAME(), 60), left(APP_NAME(), 200), getdate(), 'Yes')

return;

end

end

else

/* if the application name does not match a name in the table... */

begin

return;

end

END