Problème avec la commande xp_delete_file

Version imprimable

05/03/2025, 12h31
mouloudéen

Problème avec la commande xp_delete_file

Bonjour,

J'ai un problème lorsque j'essaie de lancer un Batch qui contient la commande xp_delete_file, qui est utilisée pour supprimer les anciens fichiers de sauvegarde.

J'ai essayé de lancer la commande via l'invite de Commandes (CMD) et j'ai obtenu l'erreur suivante:

Code:

The EXECUTE permission was denied on the object 'xp_delete_file', database 'mssqlsystemresource', schema 'sys'

1. Comment puis-je accorder l'autorisation d'exécution sur la PS XP_DELETE_FILE vu qu'elle n'est pas visible dans dans le volet PS Système

Note: la session que j'utilise a le plein droit sur le répertoire qui contient les fichiers de sauvegarde à supprimer.

Si bien entendu. Il suffit de savoir utiliser SSMS. Vous la trouverez comme toutes les procédures stockées étendues dans la base master sous :
Programmabilité
- procédure stockées
-- procédure stockées étendues
--- procédure stockées étendues systèmes

Sinon, depuis n'importe quelle base, par requête :

Code:

1
2
3
SELECT * 
FROM   sys.all_objects 
WHERE  name = 'xp_delete_file'

Pour définir son privilège execute sur un utilisateur de master :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
USE master;
GO
 
CREATE LOGIN CNX_TEST
   WITH PASSWORD = 'toto';
 
CREATE USER USR_TEST 
   FOR LOGIN CNX_TEST;
 
GRANT EXECUTE 
   ON xp_delete_file 
   TO USR_TEST;

Voir à faire une procédure dans une base qui encapsule une dépersonnalisation :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE USER USR_TEST_DELETE_FILE WITHOUT LOGIN;
GO
 
CREATE PROCEDURE P_DELETE_BACKUP_FILE 
   @FILE_TYPE TINYINT = 0,          -- 0 for backup files or 1 for report files.
   @PATH NVARCHAR(256) = '',        -- The folder to delete files. The path must end with a backslash "\".
   @EXTENSION NVARCHAR(16) = 'BAK', -- This could be 'BAK' or 'TRN' or whatever you normally use.
   @DATE_MAX DATETIME = '19800101', -- Date until to deleted files.
   @SUB TINYINT = 0                 -- 0 to ignore subfolders, 1 to delete files in subfolders.
WITH EXECUTE AS OWNER
AS
 
EXEC xp_delete_file @FILE_TYPE, @PATH, @EXTENSION, @DATE_MAX, @SUB;
 
GO
 
GRANT EXECUTE ON P_DELETE_BACKUP_FILE TO USR_TEST_DELETE_FILE;
GO

A +

06/03/2025, 10h17
mouloudéen
Tout d'abord je vous remercie pour la réponse

Citation:

Envoyé par SQLpro

Si bien entendu. Il suffit de savoir utiliser SSMS. Vous la trouverez comme toutes les procédures stockées étendues dans la base master sous :
Programmabilité
- procédure stockées
-- procédure stockées étendues
--- procédure stockées étendues systèmes

A +

La procédure que j'essaye d'utiliser de mon script est la suivante dbo.dx_delete_file alors que dans le liste des procédures stockées étendues systèmes affichées par le SSMS j'ai trouvé la procédure sys.xp_delete_file.

Est ce que ces deux SPs sont les mêmes ?
Citation:
Envoyé par SQLpro

Pour définir son privilège execute sur un utilisateur de master :

Code:

1 2 3 4 5 6 7 8 USE master; GO ... GRANT EXECUTE ON xp_delete_file TO USR_TEST;

A +
J'ai exécuté la commande GRANT sur mon utilisateur, mais lorsque j'essaye d'exécuter mon batch je reçois le message suivant:

Citation:

Error executing 'xp_delete_file': Permission denied. User must be a member of 'sysadmin' server side.

Est ce qu'il y a une solution pour lancer cette commande sans assigné le rôle sysadmin à la session qui exécute le batch ?
10/03/2025, 15h32
Michel.Priori

Citation:

Envoyé par mouloudéen

La procédure que j'essaye d'utiliser de mon script est la suivante dbo.dx_delete_file alors que dans le liste des procédures stockées étendues systèmes affichées par le SSMS j'ai trouvé la procédure sys.xp_delete_file.

Est ce que ces deux SPs sont les mêmes ?

Je pense que tu connais la réponse ;)

Petit rappel sur les la nomenclature des objets : [[[<instance>.][<database>.][<schéma>.]objet

Ainsi
[server1\express].msdb.dbo.bakupset identifie la vue backupset du schéma dbo, dans la base msdb, de l'instance server1\express
Les 3 premières parties sont remplacées par les valeurs par défaut:
* instance = l'instance qui a admis la connexion (sinon on se connecte à un serveur lié)
* database = la base actuelle (USE ...)
* schéma = le schéma par défaut de l'utilisateur de la base correspondant à la session en cours ; en général c'est dbo

Comme tu le soulignes, pas le même nommage = pas le même objet

Maintenant faut aller voir ce que contient la procédure comme instruction pour pouvoir donner les "bons" droits.

Le truc m'a l'air assez packagé ; y a pas une doc qui traine ?
11/03/2025, 11h29
mouloudéen

Citation:

Envoyé par Michel.Priori

Je pense que tu connais la réponse ;)

Oui effectivement, je connaissais la réponse :D mais moi je voulais dire, indirectement, que je n'ai pas trouvé la SP dbo.dx_delete_file par les méthodes proposées par SQLpro

Citation:

Si bien entendu. Il suffit de savoir utiliser SSMS. Vous la trouverez comme toutes les procédures stockées étendues dans la base master sous :
Programmabilité
- procédure stockées
-- procédure stockées étendues
--- procédure stockées étendues systèmes

Sinon, depuis n'importe quelle base, par requête :
Code : Sélectionner tout - Visualiser dans une fenêtre à part

SELECT *
FROM sys.all_objects
WHERE name = 'xp_delete_file'
Pour définir son privilège execute sur un utilisateur de master :
11/03/2025, 14h10
SQLpro
Citation:

Envoyé par mouloudéen;12074052... je n'ai pas trouvé la SP [B

dbo[/B].dx_delete_file par les méthodes proposées par SQLpro

Tout simplement par ce qu'elle n'existe pas... Les procédures livrées par Microsoft ont un nom commençant par :
Code:

1 2 sp_ xp_
A +
11/03/2025, 14h12
Michel.Priori

Citation:

Envoyé par mouloudéen

J'ai essayé de lancer la commande via l'invite de Commandes (CMD) et j'ai obtenu l'erreur suivante:

Code:

The EXECUTE permission was denied on the object 'xp_delete_file', database 'mssqlsystemresource', schema 'sys'

Re,
Après avoir relu les messages où il est question de dbo.dx_delete_file, puis de sys.xp_delete_file, j'ai relu le post initial.

Est-ce que la situation est : l'exécution du contenu de la commande via SSMS fonctionne alors que via CMD elle plante ?

Par ailleurs quelle est la ligne de commande CMD qui est exécutée ?
12/03/2025, 14h05
mouloudéen
Citation:
Envoyé par SQLpro

Citation:

Citation Envoyé par mouloudéen;12074052... je n'ai pas trouvé la SP [B
dbo[/B].dx_delete_file par les méthodes proposées par SQLpro

Tout simplement par ce qu'elle n'existe pas... Les procédures livrées par Microsoft ont un nom commençant par :

Code:

1 2 sp_ xp_
C'était juste une erreur de frappe, La procédure commence bien par un xp_ (dbo.xp_delete_file[/COLOR])

Cependant, après plusieurs recherche j'ai trouvé que la PS est implémentée comme une DDL nommée xpstar90.dll. Peut être le nom de DLL a changé dans les nouvelles versions de SQL Server car l'article que j'ai trouvé date de 2011 (Voir ICI)

Citation:

Envoyé par Michel.Priori

Est-ce que la situation est : l'exécution du contenu de la commande via SSMS fonctionne alors que via CMD elle plante ?

La commande marche uniquement avec un utilisateur ayant le privilège sysadmin

Citation:

Envoyé par Michel.Priori

Par ailleurs quelle est la ligne de commande CMD qui est exécutée ?

En fait, via SQLCMD je lance un script (fichier .sql) comme suit:

Code:

"C:\Program File\Microsoft SQl Server\....\SQLCMD.EXE" -S NomServer\NomInstance -i "D:\MyScript.sql"

Voici le contenu du fichier MyScript.sql
Code:

1 2 3 4 DECLARE @Cutoff_Date AS NVARCHAR(19) SET @Cutoff_Date = CONVERT(NVARCHAR(19), DATEADD(HOUR, -60, GETDATE()), 120 ) EXECUTE master.dbo.xp_delete_File 0, N'D:\TransactionLogs', N'bak', @Cutoff_Date ,1

13/03/2025, 14h26
Michel.Priori

Citation:

Envoyé par mouloudéen

La commande marche uniquement avec un utilisateur ayant le privilège sysadmin

Oui.
Les comptes membres du rôle sysadmin (par exemple SA) agissent en tant que le compte qui démarre le service MSSQL.

Citation:

Envoyé par mouloudéen

En fait, via SQLCMD je lance un script (fichier .sql) comme suit:

Code:

"C:\Program File\Microsoft SQl Server\....\SQLCMD.EXE" -S NomServer\NomInstance -i "D:\MyScript.sql"

Sans option spécifique au sujet du compte utilisé pour la connexion, SQLCMD utilise l'option -E : authentifiction Windows = le compte qui lance la commande.
Si ce compte ne fait pas partie du rôle sysadmin, il faut
1. faire un "credential" (dans SSMS, <instance>\security\credential <clic droit> new crendential
2. donner le droit d'utiliser la commande qui fait appel au shell au compte en question : GRANT execute ...

Citation:
Envoyé par mouloudéen
...
Voici le contenu du fichier MyScript.sql
Code:

1 2 3 4 DECLARE @Cutoff_Date AS NVARCHAR(19) SET @Cutoff_Date = CONVERT(NVARCHAR(19), DATEADD(HOUR, -60, GETDATE()), 120 ) EXECUTE master.dbo.xp_delete_File 0, N'D:\TransactionLogs', N'bak', @Cutoff_Date ,1
Ceci n'est pas correct. Il faut un DATETIME et non pas un VARCHAR !

De plus ne pas préciser ni le nom de la base ni le schéma (ce n'est pas dbo mais sys) pour les procédures système qui peuvent être excutées depuis n'importe quel contexte de base de données.

Enfin terminez vos commandes par le caractères point virgule ( ; )

Donc :
Code:

1 2 3 DECLARE @Cutoff_Date AS DATETIME; SET @Cutoff_Date = DATEADD(HOUR, -60, GETDATE()); EXECUTE xp_delete_File 0, N'D:\TransactionLogs', N'bak', @Cutoff_Date ,1;
Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...sous_ms_sql_se

A +

16/03/2025, 10h10
mouloudéen

Citation:

Envoyé par Michel.Priori

Oui.
Si ce compte ne fait pas partie du rôle sysadmin, il faut
1. faire un "credential" (dans SSMS, <instance>\security\credential <clic droit> new crendential
2. donner le droit d'utiliser la commande qui fait appel au shell au compte en question : GRANT execute ...

Oui le compte ne fait pas partie de sysadmin.

SVP, Est ce que vous pouvez partager un petit exemple montrant comment on peut utiliser les credentials pour exécutant une commande (master.dbo.xp_delete) nécessitant le privilège sysadmin avec user qui ne fait pas de ce rôle ?

Citation:
Envoyé par SQLpro
Donc :
Code:

1 2 3 DECLARE @Cutoff_Date AS DATETIME; SET @Cutoff_Date = DATEADD(HOUR, -60, GETDATE()); EXECUTE xp_delete_File 0, N'D:\TransactionLogs', N'bak', @Cutoff_Date ,1;
Idem ça ne marche pas sans l'attribution du privilège sysadmin à l'utilisateur

17/03/2025, 11h05
Michel.Priori

Citation:

Envoyé par mouloudéen

Oui le compte ne fait pas partie de sysadmin.
SVP, Est ce que vous pouvez partager un petit exemple montrant comment on peut utiliser les credentials pour exécutant une commande (master.dbo.xp_delete) nécessitant le privilège sysadmin avec user qui ne fait pas de ce rôle ?

Voir la BOL : https://learn.microsoft.com/fr-fr/sq...l-server-ver16
Même si ça parait évident : valider que le compte windows en question a bien le droit de faire l'opération voulue ...
Et, pour respecter la règle du moindre privilège, merci de ne pas mettre ce compte admin du domaine ni même du serveur (déjà vu ...)

Une fois le crential créé, le simple fait de donner le droit (GRANT) d'executer la commande va faire que le compte soit utilisé.
17/03/2025, 11h06
Michel.Priori

Citation:

Envoyé par mouloudéen

Idem ça ne marche pas sans l'attribution du privilège sysadmin à l'utilisateur

L'optimisation du code ne répond pas au problème de droit ...
Néanmoins la remarque de SQLpro n'est pas infondée !