Configuring login triggers

You must have sso_role status 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 >

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.

Configuring the login trigger

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. The default database checks to see whether the login has execute permissions on the stored procedure, but not until the user actually logs in to Adaptive Server and executes the login trigger.

Dropping and changing 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

Displaying the login trigger

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
....