Discussion :

[mouloudéen]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[mouloudéen]

Tout d'abord je vous remercie pour la réponse

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 ?

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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:

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 ?

[Michel.Priori]

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 ?

[mouloudéen]

Je pense que tu connais la réponse

Oui effectivement, je connaissais la réponse 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

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 :

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
sp_
xp_

A +

[Michel.Priori]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[mouloudéen]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Voici le contenu du fichier MyScript.sql

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Michel.Priori]

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.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"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 ...

[mouloudéen]

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 ?

[Michel.Priori]

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é.

[SQLpro]

...
Voici le contenu du fichier MyScript.sql

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[mouloudéen]

Donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Michel.Priori]

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 !