You may take the performance optimisation to the extreme, minimising the number of checks required for "ordinary" logons:
BEGIN
IF ORIGINAL_LOGIN() = '<the restricted user>'
-- if it's not the restricted user we are not interested in anything else. Further checks for that restricted user only.
BEGIN
IF APP_NAME() IN ('.Net SqlClient Data Provider' ,'EntityFramework')
-- If restricted user logs in from the allowed application we do not need to check for hosts.
RETURN
ELSE
BEGIN
-- only if all previous checks did not clear the alarm we're going to query allowed host names.
IF NOT EXISTS (SELECT 1
FROM <table of allowed host names> AS ah
WHERE ah.HostName = HOST_NAME())
BEGIN
ROLLBACK;
INSERT INTO <denied connections auditing table>
(
[User]
,HostName
,LoginTime
,SessionId
,AppName
)
SELECT ORIGINAL_LOGIN()
,HOST_NAME()
,GETDATE()
,@@SPID
,APP_NAME()
END
END
END
END;
I did not check the code for errors, it's a template only.
_____________
Code for TallyGenerator