When you say, "without using triggers", do you mean any triggers or just row-by-row triggers on tables?
I ask because you may be able to get what you want with judicious use of the
CONTEXT_INFO() function, but you would need to ensure that
SET CONTEXT_INFO was called correctly before your operations take place.
One place to do that might be a server-level logon trigger (i.e. not a database/object-level trigger), like so:
CREATE TRIGGER tr_audit_login
ON ALL SERVER
WITH EXECUTE AS 'sa'
DECLARE @eventdata XML = EVENTDATA();
IF @eventdata IS NOT NULL BEGIN
DECLARE @spid INT;
DECLARE @client_host VARCHAR(64);
SET @client_host = @eventdata.value('(/EVENT_INSTANCE/ClientHost)', 'VARCHAR(64)');
SET @spid = @eventdata.value('(/EVENT_INSTANCE/SPID)', 'INT');
-- pack the required data into the context data binary
-- (spid is just an example of packing multiple data items in a single field: you would probably use @@SPID at the point of use, instead)
DECLARE @context_data VARBINARY(128);
SET @context_data = CONVERT(VARBINARY(4), @spid)
+ CONVERT(VARBINARY(64), @client_host);
-- persist the spid and host into session-level memory
SET CONTEXT_INFO @context_data;
/* do better error handling here...
* logon trigger can lock all users out of server, so i am just swallowing everything
DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('%s', 10, 1, @msg) WITH LOG;
You could then add the default constraint to your table, to store context (for speed of insert):
ALTER TABLE cdc.schema_table_CT
ADD ContextInfo varbinary(128) NULL DEFAULT(CONTEXT_INFO())
Once you have that, you could query that
ContextInfo column with a bit of slice-and-dice:
,spid = CONVERT(INT, SUBSTRING(ContextInfo, 1, 4))
,client = CONVERT(VARCHAR(64), SUBSTRING(ContextInfo, 5, 64))
Technically, you could do that
CONVERT stuff as part of your default constraint, and just store the client IP there, but it may be quicker to store the whole context there (as it is done on every
INSERT), and only extract the values in a
SELECT when you need them.
I might be inclined to wrap all my
CONVERT calls in a single-row inline table-valued function, which I would
CROSS APPLY when necessary. That keeps the unpacking logic in one place:
CREATE FUNCTION fn_context (
AS RETURN (
spid = CONVERT(INT, SUBSTRING(@context_info, 1, 4))
,client = CONVERT(VARCHAR(64), SUBSTRING(@context_info, 5, 64))
FROM cdc.schema_table_CT s
CROSS APPLY dbo.fn_context(s.ContextInfo) c
CONTEXT_INFO is only a 128-byte
VARBINARY. If you need more data than you can fit in 128 bytes, I would create a table to hold all that data, insert as row for that 'session' into the table in the logon trigger and set
CONTEXT_INFO to the surrogate key value of that table
You should also note that, because it is only a default constraint, it is trivial for a suitably-privileged user to overwrite that context data in the at-rest table. Of course, the same is true for all the other columns in 'audit'-style tables, too.
It would be nice if it could be a persisted computed column, rather than a default, but the
CONTEXT_INFO() function is non-deterministic, so it is a no-go (you might be able to use some
FUNCTION trickery around a
VIEW, but I wouldn't).
It is also trivial for that user with sufficient access to call
SET CONTEXT_INFO themselves and mess up your day (e.g. with fake values, or specially-crafted stored injection), so treat the contents with suspicion and care, encode it prior to display, and handle exceptions well.
As for hostname, I think the
ClientHost element of
EVENTDATA() gives you the IP address (or a
<local machine> indicator). While you technically could use CLR to do reverse-DNS lookups back to hostname, these tend to be too slow to do for every
INSERT, so I would recommend not to do so.
If you have to have a hostname, you might want to use a SQL Agent job to periodically populate a separate table with the current leases from your local DHCP server or DNS zone file, as an out-of-band process, and
LEFT JOIN to that in future queries (or wrap in a scalar
FUNCTION to provide a value to a default constraint, for point-in-time).
Again, you should note that, if the application has any kind of public-facing component, IP addresses and hostnames are unreliable (e.g. due to NAT). Even if it is not public-facing, there is a certain time-based component to most IP/hostname maps, that you may need to factor.
Finally, before implementing your login trigger, it might be worthwhile turning on your server's dedicated admin connection. If the login trigger breaks in any way, it can prevent all users logging in (including the sysadmin accounts):
-- you may want to do this, so you have a back-out if the login trigger breaks login
EXEC sp_configure 'remote admin connections', 1
If you do get locked out, the DAC can be used to drop or disable the login trigger:
C:\> sqlcmd -S localhost -d master -A
1> DISABLE TRIGGER tr_audit_login ON ALL SERVER