-- configure the login trigger
sp_modifylogin my_login, 'login script', my_proc

-- drop the login trigger
sp_modifylogin my_login, 'login script', NULL

-- change the login trigger
sp_modifylogin my_login, 'login script', a_different_proc

1> sp_displaylogin my_login
2> go
(...)
Default Database: my_db
Default Language:
Auto Login Script: my_proc
Configured Authorization:
(...)

-- configure the login trigger
sp_logintrigger 'my_db.dbo.my_globaltrigger'

-- configure the login trigger
sp_modifylogin NULL, 'login script', 'my_db.dbo.my_globaltrigger'

-- drop the global login trigger
sp_logintrigger 'drop'

-- change the global login trigger
sp_logintrigger 'your_db.dbo.a_different_proc'

(substitute 'sp_logintrigger' for 'sp_modifylogin NULL, "login script", ' 
as you like...)

-- display the current global login trigger setting
1> sp_logintrigger 
2> go
 Global login trigger       Status
 -------------------------- -------------
 my_db.dbo.my_globaltrigger Enabled

1> select @@logintrigger
2> go
 --------------------------
 my_db.dbo.my_globaltrigger

(...) background task message: Hello!
(...) background task error 123456: This is test message 123456

create procedure my_proc 
as
  declare @cnt int, @limit int, @loginname varchar(32)

  select @limit = 2 -- max nr. of concurrent logins

  select @cnt = count(*) -- determine current #sessions
  from master.dbo.sysprocesses 
  where suid = suser_id()

  if @cnt > @limit -- check the limit
  begin
     select @loginname = suser_name()
     print 'Aborting login [%1!]: exceeds session limit [%2!]',
           @loginname, @limit
     select syb_quit() -- abort this session
  end

% isql -SASE125 -Umy_login -Pmypasswd
1> select 1
2> go
CT-LIBRARY error:
ct_results(): network packet layer: internal net 
library error: Net-Library operation terminated due to
disconnect

(...) background task message: Aborting login [my_login]: 
exceeds session limit [2]

• don't put any login triggers on the 'sa' login; you don't want to be locked out of the ASE server because the 'sa' login is effectively locked by a failing login trigger.
  

• don't do extensive processing in a login trigger. Anything that may take longer than at most a few seconds, or has a risk of being blocked or deadlocked, should better be avoided.
  Executing waitfor statements doesn't look like a good idea either.

• don't do tricky things with CIS or XP server in login triggers. There's some interesting ideas for creatively using these features elsewhere on this web site , but I'd rather not do this in login triggers.
  

• try to avoid elementary programming mistakes, especially in a global login trigger. Imagine what happens when, for example, your global login trigger inadvertently *always* calls syb_quit()? Right: nobody, including you yourself, the DBA, will be able to log in anymore...
  Just in case this happens anyway, restart the ASE server with traceflag 4072, which will disable the global login trigger. (and when you kill the ASE server, please don't use kill -9, but use kill -15 instead -- see chapter 3 of my Tips & Tricks book for the full story).
  

• Once a stored procedure is configured to be a login trigger, that stored procedure cannot be dropped anymore. This makes developing and testing a login trigger stored procedure rather clumsy because the login trigger must be unconfigured every time before dropping and recreating the procedure. This may be even more inconvenient because configuring a login trigger requires sso_role.
  To avoid such problems, it's a good idea to use a dummy top-level procedure as the login trigger, which does calls a second stored procedure containing all functionality. This second procedure can be dropped and recreated easily:

-- this proc. contains the login trigger functionality
create procedure proc_body
as
   -- procedure code goes here...
go

-- this procedure is configured as the login trigger 
create procedure proc_top 
as
   exec proc_body
go

sp_modifylogin my_login, 'login script', proc_top
go

• It is possible to obtain session information (such as the loginname which has caused the login trigger to be executed) using the regular built-in functions and global variables. Additional information can be obtained by querying the sysprocesses table, as illustrated in the below example:

create procedure my_proc_2 
as 
   declare @s varchar(12), @loginname varchar(32)

   select @s = status from master.dbo.sysprocesses 
   where spid = @@spid

   select @loginname = suser_name()

   print 'spid=[%1!]; login=[%2!]; status=[%3!]', 
         @@spid, @loginname, @s

One interesting thing is that the above code can identify whether it is executing as a login trigger or as a regular stored procedure based on the value of the sysprocesses.status column. When executed as a login trigger, the output is as follows:

(...) background task message: spid=[22]; 
login=[my_login]; status=[background]

When executed as a regular procedure, the output looks like this:

spid=[13]; login=[my_login]; status=[running]

• When using login triggers, always check the ASE errorlog for error messages. Things may have gone wrong which did or did not cause problems for the session, but the only way to find out is to check the errorlog. One consequence is that you shouldn't use login triggers if you don't have access to the ASE errorlog, or if the errorlog is not routinely checked for error messages.
  
  

• When logging in to ASE using a client application like isql , this client application is unaware of the existence execution of a login trigger. It will therefore present a command prompt immediately after the successful login. However, any commands submitted at the command isql prompt will not be executed by ASE until the login trigger has successfully completed execution. As shown in the next point, the prompt will still be displayed even if the login trigger has terminated the user connection.
  
  

• The login trigger stored procedure should be executable for the user logging into ASE; if no execute permission has been granted, an error message will occur in the ASE errorlog and the user connection will be terminated immediately (though isql will still show a command prompt -- see the previous point).

% isql -SASE125 -Umy_login -Pmypasswd 
1> -- no exec permission; connection will be closed,
2> -- any command will fail
3> select 1
4> go
CT-LIBRARY error:
ct_results(): network packet layer: internal net 
library error: Net-Library operation terminated due to
disconnect

When this happens, the ASE errorlog will contain a message like: EXECUTE permission denied on object my_proc, database my_db, owner dbo

• The login trigger stored procedure should not have any parameters for which no default values are specified. If it does, the login trigger will fail and the connection will be closed as in the example in the previous point. The ASE errorlog will contain messages like Procedure my_proc expects parameter @param1, which was not supplied. .

• Some errors that may occur during execution of the login trigger are not captured in the ASE errorlog and may go unnoticed ( see here ). It may therefore be difficult to debug a failing login trigger.