Suite à une discussion sur le forum Developpez.com, une demande intéressante a été émise par Ptit_Dje concernant la possibilité de donner à un compte de connexion la possibilité de sauvegarder l’ensemble des bases sur le serveur. Nativement il n’est pas possible de faire cela avec SQL Server. En effet, on ne peut octroyer le droit de sauvegarde qu’au niveau base de données.

La seule possibilité qui s’offre à nous est donc de lier pour chaque base de données un utilisateur à un compte de connexion. Chaque utilisateur de base de données doit ensuite être configurer afin de pouvoir sauvegarder la base de données concernant. Pour cela il est possible d’utiliser l’instruction GRANT ou d’associer l’utilisateur au rôle fixe de bases de données db_backupoperator. La question qui se pose maintenant est comment faire cela automatiquement lorsqu’une base est créée ? Les triggers DDL peuvent être une solution dans notre cas.

Le script suivant effectue les actions suivantes après qu’une base de données soit créée :

- Création d’un utilisateur dans la base de données nouvellement créée (nom identique à celui du compte de connexion qui lui est associé)
- Ajout du nouvel utilisateur au rôle fixe de bases de données db_backupoperator

Ce script peut bien entendu être amélioré ou modifié selon le contexte. Le but ici est simplement d’en montrer la trame principale.

 

CREATE TRIGGER TR_backupoperator
ON ALL SERVER
AFTER CREATE_DATABASE
AS

SET NOCOUNT ON;

DECLARE @user VARCHAR(100);
DECLARE @sql VARCHAR(1000);
DECLARE @xml_event XML;

SET @xml_event = EVENTDATA();
SET @user = ‘user';

SELECT @sql =
    ‘USE ‘ +
    CONVERT(SYSNAME, @xml_event.query(‘data(/EVENT_INSTANCE/DatabaseName)’)) + ‘; CREATE USER ‘ + @user +
    ‘ FOR LOGIN ‘ + @user + ‘; EXEC sp_addrolemember  »db_backupoperator »,  »’ + @user +  »';'; 

BEGIN TRY   
    EXEC(@sql);
END TRY
BEGIN CATCH
    PRINT ‘The database can »t be created due to the following issue :';
    PRINT ERROR_MESSAGE();
END CATCH;

 

Je tiens quand même à préciser que ce genre de méthode, bien que pratique, doit être utilisée avec parcimonie. En effet, dans des environnements où les données peuvent être sensibles, il n’est pas rare de vouloir isoler une base de données par rapport à une autre. Dans ce cas le fait de donner le droit de sauvegarde sur l’ensemble du serveur à un compte de connexion peut compromettre la sécurité des données car celui-ci peut avoir accès complet aux données de l’instance.

David BARBARIN (Mikedavem)
MVP SQL Server