You must have sso_role enabled to set, change, or drop a login trigger. The object ID of the login trigger is stored in the syslogins.procid column. Login triggers do not exist by default. They must be registered using alter login.
Run this command from the user’s default database. The stored procedure you are registering as a login trigger must be available in the user’s default database, because Adaptive Server searches the sysobjects table in the user’s default database to find the login trigger object.
The following example configures the stored procedure my_proc (which must exist in the database you want to configure) as a login trigger for Adaptive Server login my_login:
alter login my_login modify login script "my_proc"
Again, you must execute the command from within the user’s default database. Adaptive Server checks to see whether the login has execute permissions on the stored procedure, but not until the user actually logs in and executes the login trigger.
Once you have configured a stored procedure as a login trigger, you cannot drop it. You must unconfigure it first, either by dropping the login trigger altogether, or by changing the login trigger to a different stored procedure. To drop the login trigger, enter:
alter login my_login drop login script
To change the login trigger to a different stored procedure, enter:
alter login my_login modify login script "diff_proc"
To display the current login trigger, use sp_displaylogin:
sp_displaylogin my_login go (....) Default Database: my_db Default Language: Auto Login Script: my_proc ....
