Comment modifier les paramètres de session SQL Server

SQL Server permet de paramétrer la session de l’utilisateur qui se connecte avec différents flags. Cependant, comment faire en sorte que toute connexion par défaut à SQL Server emprunte les mêmes flags définis une fois pour toute ?

Voici quelque un des flags de session :

SET DEADLOCK_PRIORITY
SET NOCOUNT
SET DATEFORMAT
SET DATEFIRST
SET ANSI_DEFAULTS

Pour la plupart des flags, en fait pour presque tous les flags binaires, ceci est possible via sp_configure et son paramètres « user options ».
Par exemple pour faire en sorte que toute connexion entame une session avec :
ANSI_DEFAULTS ON, NOCOUNT ON, XACT_ABORT OFF
Il suffit de lancer :

EXEC sp_configure 'user options', 18238
RECONFIGURE

Comment trouver la valeur 18238 me direz vous ? Simple : il suffit d’additionner les poids des différents paramètres selon la règles suivante :


Indicateur              Poids
----------------------- -----
DISABLE_DEF_CNST_CHK    1
IMPLICIT_TRANSACTIONS   2
CURSOR_CLOSE_ON_COMMIT  4
ANSI_WARNINGS           8
ANSI_PADDING            16
ANSI_NULLS              32
ARITHABORT              64
ARITHIGNORE             128
QUOTED_IDENTIFIER       256
NOCOUNT                 512
ANSI_NULL_DFLT_ON       1024
ANSI_NULL_DFLT_OFF      2048
CONCAT_NULL_YIELDS_NULL 4096
NUMERIC_ROUNDABORT      8192
XACT_ABORT              16384

Sachant aussi que ANSI_DEFAULTS ON, signifie l’activation des indicateurs suivants : ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, QUOTED_IDENTIFIER.

On peut aussi avoir une vision plus claire et tabularisée en utilisant la requête suivante :


SELECT 'DISABLE_DEF_CNST_CHK' AS AN_OPTION,  
        CASE WHEN @@OPTIONS & 1 = 1 THEN 1 ELSE 0 END AS IS_SET
UNION ALL
SELECT 'IMPLICIT_TRANSACTIONS',
        CASE WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
UNION ALL
SELECT 'CURSOR_CLOSE_ON_COMMIT',
        CASE WHEN @@OPTIONS & 4 = 4 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ANSI_WARNINGS',
        CASE WHEN @@OPTIONS & 8 = 8 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ANSI_PADDING',
        CASE WHEN @@OPTIONS & 16 = 16 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ANSI_NULLS',
        CASE WHEN @@OPTIONS & 32 = 32 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ARITHABORT',
        CASE WHEN @@OPTIONS & 64 = 64 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ARITHIGNORE',
        CASE WHEN @@OPTIONS & 128 = 128 THEN 1 ELSE 0 END
UNION ALL
SELECT 'QUOTED_IDENTIFIER',
        CASE WHEN @@OPTIONS & 256 = 256 THEN 1 ELSE 0 END
UNION ALL
SELECT 'NOCOUNT',
        CASE WHEN @@OPTIONS & 512 = 512 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',
        CASE WHEN @@OPTIONS & 1024 = 1024 THEN 1 ELSE 0 END
UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',
        CASE WHEN @@OPTIONS & 2048 = 2048 THEN 1 ELSE 0 END
UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',
        CASE WHEN @@OPTIONS & 4096 = 4096 THEN 1 ELSE 0 END
UNION ALL
SELECT 'NUMERIC_ROUNDABORT',
        CASE WHEN @@OPTIONS & 8192 = 8192 THEN 1 ELSE 0 END
UNION ALL
SELECT 'XACT_ABORT',
        CASE WHEN @@OPTIONS & 16384 = 16384 THEN 1 ELSE 0 END

Qui donne par exemple :


AN_OPTION               IS_SET
----------------------- -----------
DISABLE_DEF_CNST_CHK    0
IMPLICIT_TRANSACTIONS   0
CURSOR_CLOSE_ON_COMMIT  0
ANSI_WARNINGS           1
ANSI_PADDING            1
ANSI_NULLS              1
ARITHABORT              1
ARITHIGNORE             0
QUOTED_IDENTIFIER       1
NOCOUNT                 0
ANSI_NULL_DFLT_ON       1
ANSI_NULL_DFLT_OFF      0
CONCAT_NULL_YIELDS_NULL 1
NUMERIC_ROUNDABORT      0
XACT_ABORT              0

Mais alors comment manipuler les autres flags qui ne font pas partie de cette liste ou sont des flags non binaires ?

Certaines flags dépendent des paramètres de la connexion. Ainsi la flag DATEFIRST dépend de la langue de connexion, donc de la culture s’y rapportant. Ainsi une connexion d’un utilisateur « french » aura un DATEFIRST à 1 (lundi) et celle d’un utilisateur « english » aura la valeur 7 pour dimanche, les anglais c’est vbien connus inversent tout, et démarrent la semaine par un pieux jour de repos (faignants tiens;-)

Pour d’autres flags, on pourra s’amuser à coder un déclencheur DDL de niveau serveur en traquant l’événement LOGON. Exemple :


CREATE TRIGGER E_DDL_LOGIN
   ON  ALL SERVER
   FOR LOGON
AS
BEGIN
 
IF ORIGINAL_LOGIN() = 'sa'
   SET DATEFORMAT DYM
 
END
GO

Un petit test va nous montrer que cela marche :

EXECUTE AS LOGIN = 'sa'
SELECT CAST('31/2008/12' AS DATETIME) AS MADATE
REVERT
 
MADATE
-----------------------
2008-12-31 00:00:00.000

CQFD !

Merci à Rudi Bruchez pour ses bonnes idées !

***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

Laisser un commentaire