This is something I needed to to recently in order to diagnose a database deadlocking problem. The solution isn’t obvious, because there is no obvious system stored procedure or view to query for this information. The solution I found was to use the DBCC USEROPTIONS command. Here is one way to use it. There are two stored procedures here. The first one just selects either all user options, or a single user option. The second one calls the first, asking specifically for the current isolation level.

create procedure UserOptions_Select
    @set_option varchar(23) = null as set nocount on create table #useroptions
(
    [Option] varchar(23),
    [Value] varchar(max)
)

insert #useroptions
exec sp_executesql N'dbcc useroptions with no_infomsgs' select [Option], [Value]
from #useroptions
where [Option] = isnull(@set_option, [Option])
GO create procedure IsolationLevel_Select
as set nocount on exec UserOptions_Select 'isolation level'
GO