go
IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_login_trigger
IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_login_trigger >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_login_trigger >>>'
END
go
create procedure dbo.sp_login_trigger
as
declare
@loginname varchar(32),
@hostname varchar(32),
@ipaddress varchar(32)
--This is an example to simply restrict user bsun from connecting to the server via a specific IP address
--To configure a login-specific login trigger:
--sp_modifylogin my_login, 'login script',sp_login_trigger
--To drop the login trigger
--sp_modifylogin my_login, 'login script',NULL
-- drop the global login trigger --sp_logintrigger 'drop' -- change the global login trigger --sp_logintrigger 'your_db.dbo.a_different_proc'
--To change the login trigger
--sp_modifylogin my_login,'login script',a_dirrerent_sp
--TO CONFIGURE a GLOBAL LOGIN TRIGGER
--sp_logintrigger 'sybsystemprocs.dbo.sp_login_trigger'
--or: sp_modifylogin NMULL,'login script','sybsystemprocs.dbo.sp_login_trigger'
select @loginname=suser_name(suid), @hostname=rtrim(clienthostname), @ipaddress=rtrim(ipaddr)
from master.dbo.sysprocesses where suid=suser_id()
--print "BEFORE IF"
--print @hostname
print @loginname
print @ipaddress
--if (@hostname='P019323' and @loginname='bsun') or (@ipaddress='192.168.23.74' and @loginname='bsun')
if @ipaddress='165.135.23.74' and @loginname='bsun'
begin
print 'Restricted User %1! using %2! blocked out', @loginname, @hostname
print 'User %1! is not allowed to connect to %2! from %3!', @loginname,@@servername,@ipaddress
/* Log an entry in an audit table */
--insert into APP_ACCESS_VIOLATION values (@loginname, @appname, getdate(), 'Access Violation')
select syb_quit()
end
go
EXEC sp_procxmode 'dbo.sp_login_trigger', 'unchained'
go
IF OBJECT_ID('dbo.sp_login_trigger') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_login_trigger >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_login_trigger >>>'
go
REVOKE EXECUTE ON dbo.sp_login_trigger FROM public
go
GRANT EXECUTE ON dbo.sp_login_trigger TO public
go
