Creating login triggers

Create a login trigger as a stored procedure. Do not use the create trigger command. The following sample creates a login trigger stored procedure in the Pubs2 database.

create loginproc as
	declare @appname					varchar(20)
	declare @attr	 				varchar(20)
	declare @value						varchar(20)
	declare @retvalue						int
declare apctx cursor for
 select appname, attr, value from
 pubs2.dbo.lookup where login = suser_name()
open apctx
fetch apctx into @appname, @attr, @value

While (@@sqlstatus = 0)
	begin
		select f@retval = 
			set_appcontext (rtrim (@appname),
			rtrim(@attr), rtrim(@value))
	fetch apctx into @appname, @attr, @value
 end
go

grant execute on loginproc to public
go

To associate a specific user with the login trigger, run sp_modifylogin in the user’s default database.