Make sure that it can't ever throw a sev 16 or higher error. 😉

What I did with a login trigger for that was kinda the following. (pseudocode). The purpose of the login trigger was to prevent people from using certain application's accounts (for which they knew the passwords) from any querying tool. I had a table with the accounts to be monitored and a second table where logging or violations was done.

CREATE TRIGGER

For Logon

AS

IF Original_login() in SELECT login name from master.dbo.restrictedlogins AND App_name in ('Query analyser',...)

BEGIN

IF DATABASEPropertyEX('DBA','Status') = 'Online'

EXEC master.dbo.LogViolation

ELSE

Print 'Login violation detected ....' -- goes into error log

ROLLBACK TRANSACTION

The only database that's guaranteed to be available is master, so the trigger should only reference master within itself. The proc that it calls after checking the DB status can reference the other DBs, as it has been checked that they are available.

If I may suggest, login triggers are possibly not the appropriate tool for logging all logins onto a server. A server-side trace capturing the Audit Login event would be much lighter on the server, and would give you the ability to import the trace onto a central auditing server (if you have one)

You could also enable the 'audit successful and failed logins' on the server properties.

Logon triggers are, imho, better for when you want to selectively prevent logins, or log under certain conditions, or run arbitrary code whenever someone logs in.