a well written trigger will not prevent users from logging in, just one that didn't take into consideration things like permissions on the target table.

some of the info, like the databasename, might not be available, as there's often no database context at teh login time, only later...it depends ont eh conection properties, i believe.

here's a link to a Login trace I like to use for an example; you would need to make the procedure a statrup procedure so it restarts when the server restarts. it also creates a view, so i can select fromt eh trace anytime i want.

sp_AddMyLogonTrace.txt

alternatively, here's a nice login trigger i've slapped togther :note it takes into consdieration the ability to write to the table

CREATE TABLE [master].[dbo].[TRACETABLE] (

[EVENTDATE] DATETIME NOT NULL,

[DBNAME] NVARCHAR(128) NULL,

[CURRENTUSER] NVARCHAR(128) NULL,

[HOSTNAME] NVARCHAR(128) NULL,

[APPLICATIONNAME] NVARCHAR(128) NULL,

[PROCEDURENAME] NVARCHAR(128) NULL,

[USERID] SMALLINT NULL,

[USERNAME] NVARCHAR(128) NULL,

[SUSERID] INT NULL,

[SUSERNAME] NVARCHAR(128) NULL,

[IS_SERVERADMIN_SYSADMIN] INT NULL,

[IS_DB_OWNER] INT NULL,

[IS_DDL_ADMIN] INT NULL,

[IS_DB_DATAREADER] INT NULL,

[ORIGINAL_LOGIN] NVARCHAR(4000) NULL,

[NET_TRANSPORT] SQL_VARIANT NULL,

[PROTOCOL_TYPE] SQL_VARIANT NULL,

[AUTH_SCHEME] SQL_VARIANT NULL,

[LOCAL_NET_ADDRESS] SQL_VARIANT NULL,

[LOCAL_TCP_PORT] SQL_VARIANT NULL,

[CLIENT_NET_ADDRESS] SQL_VARIANT NULL,

[PHYSICAL_NET_TRANSPORT] SQL_VARIANT NULL)

GO

GRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLIC

GRANT SELECT on [master].[sys].[dm_exec_connections]

GO

CREATE TRIGGER Logon_Trigger_Track_IP

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO [master].[dbo].[TRACETABLE]

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

CURRENT_USER AS CurrentUser,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

END

GO

ENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER