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:
USE master
GO
CREATE TRIGGER tr_audit_login
ON ALL SERVER
WITH EXECUTE AS 'sa'
AFTER LOGON
AS BEGIN
BEGIN TRY
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)[1]', 'VARCHAR(64)');
SET @spid = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', '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;
END
END TRY
BEGIN CATCH
/* 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;
END CATCH
END
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:
SELECT *
,spid = CONVERT(INT, SUBSTRING(ContextInfo, 1, 4))
,client = CONVERT(VARCHAR(64), SUBSTRING(ContextInfo, 5, 64))
FROM cdc.schema_table_CT
Technically, you could do that SUBSTRING and 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 SUBSTRING and 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 (
@context_info VARBINARY(128)
)
RETURNS TABLE
AS RETURN (
SELECT
spid = CONVERT(INT, SUBSTRING(@context_info, 1, 4))
,client = CONVERT(VARCHAR(64), SUBSTRING(@context_info, 5, 64))
)
GO
SELECT *
FROM cdc.schema_table_CT s
CROSS APPLY dbo.fn_context(s.ContextInfo) c
Note that 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):
USE master
GO
-- 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
GO
RECONFIGURE
GO
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
2> GO
