Nous avons tous nos scripts, outils ou utilitaires préférés qui facilitent notre travail. Mais parfois, nous devons mettre à niveau nos scripts pour les rendre encore meilleurs. Une façon de le faire est de les partager avec d’autres. En partageant nos scripts, nous aidons non seulement les autres, mais nous recevons également des commentaires précieux pour améliorer nos propres scripts.

Dans le 104e épisode de TSQL Tuesday, Bert Wagner nous invite à écrire sur le code dont nous ne pourrions pas nous passer. Il peut s’agir d’un script de maintenance, d’une requête de surveillance des statistiques système, ou même d’un code PowerShell pour le nettoyage des données. L’objectif est de partager notre travail et d’apprendre des autres.

Le partage de votre script peut bénéficier à d’autres personnes qui pourraient être confrontées à des défis similaires. Cela ouvre également la possibilité à d’autres de suggérer des améliorations ou des solutions alternatives. Même si personne d’autre ne bénéficie de votre script, vous disposez toujours d’une ressource précieuse stockée quelque part avec des instructions sur ce qu’il fait, facilement accessible pour une utilisation future.

Personnellement, j’ai trouvé et réutilisé certains de mes anciens scripts. Non seulement je les redécouvre, mais je peux aussi imaginer de nouvelles utilisations ou améliorations pour le script.

Accès facile

En réfléchissant à ce sujet, j’ai réalisé que je publie fréquemment des scripts sur mon blog. Au lieu de répéter quelque chose que j’ai déjà partagé, je voulais fournir quelque chose de différent.

Un domaine dans lequel de nombreuses organisations ont du mal est l’audit de leurs instances SQL Server sans avoir recours à des logiciels tiers coûteux ou à des fonctionnalités de l’édition Enterprise. Dans un cas particulier, j’avais un client avec un développeur qui avait trouvé une porte dérobée pour obtenir un accès SA et effectuait des modifications non autorisées. Pour remédier à cela, j’ai créé un script qui utilisait la trace par défaut pour capturer les modifications des autorisations et des principaux sur le serveur.

Les données capturées sont ensuite stockées dans une base de données distincte, accessible uniquement à quelques personnes sélectionnées. Cela permet d’interroger régulièrement les données ou de créer des rapports automatisés pour envoyer des notifications par e-mail de toutes les modifications rencontrées.

Voici un exemple de la requête utilisée :

INSERT INTO DBA.[AUDIT].[DefTracePermissions]
([SvrName]
,[EventTimeStamp]
,[EventCategory]
,[spid]
,[subclass_name]
,[LoginName]
,[DBUserName]
,[HostName]
,[DatabaseName]
,[ObjectName]
,[TargetUserName]
,[TargetLoginName]
,[SchemaName]
,[RoleName]
,[TraceEvent]
,[ApplicationName])
SELECT [SvrName]
,[EventTimeStamp]
,[EventCategory]
,[spid]
,[subclass_name]
,[LoginName]
,[DBUserName]
,[HostName]
,[DatabaseName]
,[ObjectName]
,[TargetUserName]
,[TargetLoginName]
,[SchemaName]
,[RoleName]
,[TraceEvent]
,[ApplicationName]
FROM OPENQUERY([SomeServer],
'DECLARE @Path VARCHAR(512)
,@StartTime DATE
,@EndTime DATE = getdate()

/* Ces plages de dates devront être modifiées */
SET @StartTime = dateadd(dd, datediff(dd, 0, @EndTime) - 1, 0)

SELECT @Path = REVERSE(SUBSTRING(REVERSE([PATH]), 
CHARINDEX('''', REVERSE([path])), 260)) + N''LOG.trc''
FROM sys.traces 
WHERE is_default = 1;

SELECT @@servername as SvrName, gt.StartTime AS EventTimeStamp, tc.name AS EventCategory, spid
,tv.subclass_name
,gt.LoginName, gt.DBUserName, gt.HostName
,gt.DatabaseName, gt.ObjectName, gt.TargetUserName, gt.TargetLoginName, gt.ParentName AS SchemaName
,gt.RoleName, te.name AS TraceEvent
FROM ::fn_trace_gettable( @path, DEFAULT ) gt
INNER JOIN sys.trace_events te
ON gt.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
INNER JOIN sys.trace_subclass_values tv
ON gt.EventSubClass = tv.subclass_value
AND gt.EventClass = tv.trace_event_id
WHERE 1 = 1
AND CONVERT(date, gt.StartTime) >= @StartTime 
AND CONVERT(date, gt.StartTime) <= @EndTime
AND tc.name = ''Security Audit''
AND gt.TargetLoginName IS NOT NULL
ORDER BY gt.StartTime;')

Le truc ici est que la requête est exécutée à partir d’un travail sur un serveur différent, avec un accès restreint à seulement quelques personnes. Cela rend plus difficile la découverte et la modification du script par des yeux indiscrets. De plus, un serveur lié est utilisé pour effectuer les requêtes, garantissant que les données capturées ne sont pas stockées localement sur le serveur.

Une fois les données interrogées, elles peuvent être stockées dans une table sur le serveur d’administration. Voici un exemple de configuration de la table :

USE [DBA]
GO
IF SCHEMA_ID('AUDIT') IS NULL
BEGIN
EXECUTE ('CREATE SCHEMA [AUDIT]');
END
CREATE TABLE [AUDIT].[DefTracePermissions](
[DTPermID] [bigint] IDENTITY(1,1) NOT NULL,
[SvrName] [varchar](128) NOT NULL,
[EventTimeStamp] [datetime] NOT NULL,
[EventCategory] [varchar](128) NULL,
[spid] [int] NULL,
[subclass_name] [varchar](128) NULL,
[LoginName] [varchar](128) NULL,
[DBUserName] [varchar](128) NULL,
[HostName] [varchar](128) NULL,
[DatabaseName] [varchar](128) NULL,
[ObjectName] [varchar](128) NULL,
[TargetUserName] [varchar](128) NULL,
[TargetLoginName] [varchar](128) NULL,
[SchemaName] [varchar](256) NULL,
[RoleName] [varchar](64) NULL,
[TraceEvent] [varchar](128) NULL,
[ApplicationName] [varchar](256) NULL,
 CONSTRAINT [PK_DefTracePermissions] PRIMARY KEY CLUSTERED 
(
[DTPermID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Une fois la table créée, le script peut être programmé pour s’exécuter régulièrement à l’aide d’un travail d’agent. Cela garantit que les données sont capturées de manière cohérente et peuvent être facilement consultées à des fins d’audit.

Le partage de vos scripts et de vos idées profite non seulement aux autres, mais vous aide également à vous développer en tant que professionnel SQL Server. En contribuant à la communauté, vous améliorez votre vie personnelle et continuez à apprendre.

Si vous êtes intéressé par d’autres contributions de la communauté, consultez la série que j’ai publiée sur mon blog.