Ponieważ najwyraźniej używasz SQL Server 2016, chciałbym wyrzucić kolejną „ możliwą ” opcję - SESSION_CONTEXT.
Artykuł Leonarda Lobela „ Stan udostępniania w SQL Server 2016”SESSION_CONTEXT zawiera bardzo dobre informacje na temat tej nowej funkcji w SQL Server 2016.
Podsumowując niektóre kluczowe punkty:
Jeśli kiedykolwiek chciałeś współużytkować stan sesji we wszystkich procedurach przechowywanych i partiach przez cały okres połączenia z bazą danych, pokochasz SESSION_CONTEXT. Po nawiązaniu połączenia z programem SQL Server 2016 otrzymujesz słownik stanowy lub coś, co jest często określane jako torba stanu, miejsce, w którym możesz przechowywać wartości, takie jak ciągi i liczby, a następnie odzyskiwać je za pomocą przypisanego klucza. W przypadku SESSION_CONTEXTklucza kluczem jest dowolny ciąg znaków, a wartością jest zmienna sql_variant, co oznacza, że może pomieścić różne typy.
Gdy coś zapiszesz SESSION_CONTEXT, pozostanie tam do momentu zamknięcia połączenia. Nie jest przechowywany w żadnej tabeli w bazie danych, po prostu żyje w pamięci, dopóki połączenie pozostaje aktywne. I każdy kod T-SQL, który działa w procedurach przechowywanych, wyzwalaczach, funkcjach lub czymkolwiek, może współdzielić to, w co się wpakuje
SESSION_CONTEXT.
Do tej pory mieliśmy do czynienia z taką najbliższą rzeczą CONTEXT_INFO, która pozwala przechowywać i udostępniać pojedynczą wartość binarną o długości do 128 bajtów, która jest znacznie mniej elastyczna niż słownik, który masz SESSION_CONTEXT, który obsługuje wiele wartości różnych danych typy.
SESSION_CONTEXTjest łatwy w użyciu, wystarczy wywołać sp_set_session_context, aby zapisać wartość według pożądanego klucza. Gdy to zrobisz, podajesz oczywiście klucz i wartość, ale możesz również ustawić parametr read_only na true. Blokuje to wartość w kontekście sesji, dzięki czemu nie można jej zmienić przez resztę życia połączenia. Na przykład aplikacja kliencka może łatwo wywołać tę procedurę przechowywaną w celu ustawienia niektórych wartości kontekstu sesji zaraz po ustanowieniu połączenia z bazą danych. Jeśli aplikacja ustawi parametr read_only, gdy to zrobi, wówczas procedury składowane i inny kod T-SQL, który następnie wykonuje się na serwerze, mogą tylko odczytać wartość, nie mogą zmienić tego, co zostało ustawione przez aplikację uruchomioną na kliencie.
Jako test stworzyłem wyzwalacz logowania do serwera, który ustawia pewne CONTEXT_SESSIONinformacje - jedna z nich SESSION_CONTEXTzostała ustawiona na @read_only.
DROP TRIGGER IF EXISTS [InitializeSessionContext] ON ALL SERVER
GO
CREATE TRIGGER InitializeSessionContext ON ALL SERVER
FOR LOGON AS
BEGIN
--Initialize context information that can be altered in the session
EXEC sp_set_session_context @key = N'UsRegion'
,@value = N'Southeast'
--Initialize context information that cannot be altered in the session
EXEC sp_set_session_context @key = N'CannotChange'
,@value = N'CannotChangeThisValue'
,@read_only = 1
END;
Zalogowałem się jako zupełnie nowy użytkownik i udało mi się wyodrębnić SESSION_CONTEXTinformacje:
DECLARE @UsRegion varchar(20)
SET @UsRegion = CONVERT(varchar(20), SESSION_CONTEXT(N'UsRegion'))
SELECT DoThat = @UsRegion
DECLARE @CannotChange varchar(20)
SET @CannotChange = CONVERT(varchar(20), SESSION_CONTEXT(N'CannotChange'))
SELECT DoThat = @CannotChange
Próbowałem nawet zmienić informacje kontekstowe „tylko do odczytu”:
EXEC sp_set_session_context @key = N'CannotChange'
,@value = N'CannotChangeThisValue'
i otrzymał błąd:
Msg 15664, poziom 16, stan 1, procedura sp_set_session_context, wiersz 1 [wiersz wsadowy 8] Nie można ustawić klucza „CannotChange” w kontekście sesji. Klucz został ustawiony jako tylko do odczytu dla tej sesji.
Ważna uwaga na temat wyzwalaczy logowania ( z tego postu )!
Wyzwalacz logowania może skutecznie uniemożliwić pomyślne połączenia z aparatem bazy danych wszystkim użytkownikom, w tym członkom stałej roli serwera sysadmin. Gdy wyzwalacz logowania uniemożliwia połączenia, członkowie stałej roli serwera sysadmin mogą łączyć się za pomocą dedykowanego połączenia administratora lub uruchamiając aparat bazy danych w trybie konfiguracji minimalnej (-f)
Jedną z potencjalnych wad jest to, że wypełnia to całą instancję kontekstu sesji (nie dla bazy danych). W tym momencie jedyne opcje, o których mogę myśleć, to:
- Nazwij
Session_Contextpary nazwa-wartość, poprzedzając je nazwą bazy danych, aby nie spowodować kolizji dla nazwy tego samego typu w innej bazie danych. To nie rozwiązuje problemu wstępnego definiowania WSZYSTKICH Session_Contextwartości dla wszystkich użytkowników.
- Po uruchomieniu wyzwalacza logowania masz dostęp do
EventData(xml), którego możesz użyć do wyodrębnienia loginu i na tej podstawie możesz utworzyć określone Session_Contextpary nazwa-wartość.