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 sp_modifylogin. The syntax is:
sp_modifylogin <login_name>, "login script", <sproc_name >
login_name – the user’s login name.
"login script" – type in as shown; “login script” tells sp_modifylogin that the next parameter, “sproc_name”, is a login trigger.
sproc_name – the name of the stored procedure configured as a login trigger for this user.
Run this procedure 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:
sp_modifylogin my_login, "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:
sp_modifylogin my_login, "login script", NULL
To change the login trigger to a different stored procedure, enter:
sp_modifylogin my_login, "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 ....
