In sql server we can get session property many ways

1. dbcc useroptions
dbcc useroptions
This will give us information about all the enabled setting for that session.

 In SQL Server 2005 two new options are available

2. sessionproperty function
 It returns set option of a session

 You can get information of following option usig set
 ANSI_NULLS
 ANSI_PADDING
 ANSI_WARNINGS
 ARITHABORT
 CONCAT_NULL_YIELDS_ NULL
 NUMERIC_ROUNDABORT
 QUOTED_IDENTIFIER
 
For ex.
 select sessionproperty('Quoted_Identifier')

 If result is 1 then property on.

3. Dynamic view dm_exec_sessions
 Benefit of this view that you can see property not only of this session but of also other sessions.You can join this view other dynamic system views using session id.
This view also gives much more information than session property.

 If the result is 1 then property is on and if its 0 then property is set off.

select session_id,
login_name,
program_name,
date_format,
quoted_identifier,
arithabort,
ansi_null_dflt_on,
ansi_defaults,
ansi_warnings,
ansi_padding,
ansi_nulls,
concat_null_yields_null,
case transaction_isolation_level 
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end as transaction_isolation_level,
deadlock_priority
from sys.dm_exec_sessions