Спасибо что многие отозвались.

Хотел бы повторить суть проблемы и подвести итог так сказать.

по сути было два вопроса 1) какой метод использовать для хранения истории (также с этим связана авторизация чтоб получить имя пользователя)
2) проблема с spid


по первому вопросу решил для себя следующее: в идеале использовать свою систему авторизации (свои таблицы с пользователями, группами, правами пользователей...) и в месте с этим использовать историю в виде доп. таблицы (где кроме записи есть поля кто,когда и что делал)

для того чтоб использовать триггеры (получить пользователя) нам необходимо использовать "маппинг" для таблицы и по
@sspid, дата, пользователь( учетка под которой зашли ) определять своего пользователя

пример :

create table dbo.SessionToUserMapping
(
session_id int constraint PK_SessionToUserMapping primary key,
login_name sysname,
login_time datetime,
custom_user_id int
);

--регистрация пользователя
merge into dbo.SessionToUserMapping t
using (
select
session_id, login_name, login_time, @custom_user_id as custom_user_id
from
sys.dm_exec_session
where
session_id = @@SPID
) s on t.session_id = s.session_id
when not matched then
insert
(session_id, login_name, login_time, custom_user_id)
values
(s.session_id, s.login_name, s.login_time, s.custom_user_id)
when matched then
update
set
login_name = s.login_name,
login_time = s.login_time,
custom_user_id = s.custom_user_id;

--получение пользователя
select
m.custom_user_id
from
dbo.SessionToUserMapping m join
sys.dm_exec_sessions s on s.session_id = m.session_id and s.login_name = m.login_name and s.login_time = m.login_time;


от сюда появляется проблема так как подключение держать на всем времени работы программы не правильно то login_time будет всегда разное из-за секунд ( если брать только дату то возможны путаницы с пользователем)

как можно исправить этот момент ?????
-регистрировать пользователя при запуске программы
- тащить пользователя при каждом обращении к бд
-или использовать еще какието параметры кроме login_time ,login_name ,session_id

пример подключения к бд

SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["BD"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "PROCEDURE";
cmd.Parameters.AddWithValue("PaRAM", PaRAM);
cmd.Parameters.Add("Result", System.Data.SqlDbType.Int);
cmd.Parameters["Result"].Direction = System.Data.ParameterDirection.Output;
bool res = false;

try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
id = Convert.ToInt32(cmd.Parameters["Result"].Value);

if (id > 0) res = true; ;
}
catch (Exception ex)
{

if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();

}

Konst_One,
вы имеете в виду намек это

"Если это сервер приложения то нужно писать линк таблицу куда писать соответствие Spid и пользователя и по ней делать сопоставление. Но в некоторых системах это может быть проблематично когда spid меняется и управлять этим нельзя или сложно"


согласен именно это и нужно пример подобной реализации в гугле подобного, корректного примера я не нашел в связи с чем и прошу помощи

(пример подобного я привел выше )

или как правильно назвать такой подход " линк таблицу куда писать соответствие Spid и пользователя"

Konst_One,

нет 1С не используется


используется c# и MSSQL

Konst_One,

"почитайте для начала про CDC из коробки: " интересная статья но не понял как определяется пользователь который сделал изменение как то об этом не рассказывают

Konst_One,
вы такими вопросами уводите в сторону

система с нуля,
авторизации на уровне сервера базы данных- авторизация своя (как писал раньше свои таблицы пользователей групп, прав...)
причем тут пул соединений ?-- на сколько помню он по умолчанию включен и используется "для скорости подключения к бд" -так очень кратко ( как писал раньше login_time будет всегда разным)
я ж это все описал раньше

Гавриленко Сергей Алексеевич,

хах)))
просто лучший ответ

тут выше примеры, и описания подходов что говорит о том что это в полне реализуемый подход и в реализации этого подхода столкнулся с проблемой о которой и спрашиваю совета

а что посоветуете использовать для авторизации?

a_voronin,

вы серьезно??

используется "Тип 4: добавить таблицу истории " ( писал об этом 8 сообщений выше)

Konst_One,
как вы так определили ??

@sspid будет выглядеть так

54
55
56
.
.
.
но так как мы подключились выполнили действие и отключились то sspid не будет висеть постоянно а будет заполнять ся другим подключением
получится ситуация

подключился пользователь "USER" присвоен 54 user сделал дело и отключился номер 54 освободился и присвоен другому

Konst_One,

"поэтому использовать spid для логирования истории изменений конкретным юзером бессмысленно" по этому используется
@sspid, login, time (выше описано)