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.