Chapitre 03 

: implémentation d’un mode

D’authentification
1. Implémentation d’un mode d’authentification :

En effet, il faut savoir que vous pouvez configurer SQL Server de 2 façons:

 Windows authentification Mode: Utilise les informations de connexion à Windows pour permettre la
connexion à SQL Server.
 Mixed Mode: Mode mixe, vous permettant, en plus du mode Windows authentification, de créer des
login/mot de passe de connexion à SQL Server.

Si vous choisissez le 2ème mode (Mixed Mode), il vous sera demandé de spécifier le mode de passe de
l'utilisateur sa: il s'agit du super administrateur de SQL Server.
Vous devez savoir que vous aurez la possibilité de changer, plus tard, ce type d'authentification dans les
propriétés de votre serveur SQL, si vous changez d'avis.

2.Créations, modification et suppression des comptes :

2.1. Compte de connexion  Serveur:

Les connexions, qu’elles soient de type SQL Server ou bien Windows, doivent être définies au niveau
de l’instance SQL Server pour permettre aux utilisateurs de se connecter.

2.1.1. Création :

a) En mode graphique (SSMS) :

_ depuis l’explorateur d’objet ,se placer sur le nœud Sécurité-connexion.

_ Depuis le menu contextuel associé au nœud de connexion, faire le choix nouvelle connexion.

_ Dans la zone Nom, entrez un nom pour la connexion Microsoft SQL Server.

_ Sous Authentification, sélectionnez Authentification SQL Server ou authentification Windows

1
_ Si l’authentification est SQL Server Vous saisir un mot de passe dans la zone Mot de passe.

_ Dans la zone Base de données, cliquez sur la base de données par défaut à laquelle la connexion se fera, une
fois la connexion à une instance de SQL Server établie.

_ Dans la zone Langue, cliquez sur la langue par défaut dans laquelle les messages seront affichés à l'utilisateur.

_ Valider Par ‘OK’

b) En Mode T_SQL : On utilise la commande CREATE.

Authentification Windows

create login nom_connexion from windows

with default_database=basededonnées,
default_language=[langue],
Authentification SQL Server

create login nom_connexion

with password='mot de passe',
default_database=basededonnées,
default_language=[langue],
check_expiration=off/on,
check_policy=on  /* Appliquer les règles de mots de passes de Windows */

Exemple pour Authentification Windows

create login [pc\mohammed] from windows

with
default_database=BD1,
default_language=[français]
Exemple pour Authentification SQL Server

create login test

with password='insfp',
default_database=BD1,
default_language=[français],
check_expiration=off,
check_policy=on

2
2.1.2) Modification :

a) En mode SSMS :

1. Choisir la connexion à modifier.

2. Cliquer sur le bouton droit de la souris et choisir ‘Propriétés ‘.

3. Changer les propriétés voulues et valider par ‘OK’

b) En Mode T_SQL : On utilise la commande ALTER.

Exemples :

1. Le code exemple suivant remplace le mot de passe de la connexion Test par INSFP.
ALTER LOGIN Test WITH PASSWORD = 'INSFP';
GO
2. Le code exemple suivant remplace le nom de la connexion Test par Test1.
ALTER LOGIN Test WITH NAME = Test1;
GO

2.1.3. Suppression :

a) En mode SSMS :

1. Choisir la connexion à supprimer.

2. Cliquer sur le bouton droit de la souris et choisir ‘Supprimer ‘.

3. une fenêtre de suppression s’affiche Valider par ‘OK’.

b) En Mode T_SQL : On utilise la commande DROP.

Exemple : Supprimer la connexion Test :

DROP LOGIN Test

Go
2.2. Comptes d’utilisateurs  de Base de Données:
Après la définition des connexions (login) au niveau du serveur, il est nécessaire de définir des utilisateurs dans
les bases de données.
C’est au niveau des utilisateurs de base de données que seront attribués les droit d’utilisation des objets définis
dans la base de données.
2.2.1. Création :

a) En mode SSMS :

Pour créer un utilisateur de base de données, il faut se positionner sur la base de données concernée par
l’ajout depuis l’explorateur d’objet et réalise la manipulation suivante :

_ développer le nœud Sécurité et se positionner sur le nœud utilisateur.

_ Depuis le menu contextuel associé au nœud Utilisateurs, sélectionner Nouvel utilisateur.

3
_ sélectionner la connexion associé à l’utilisateur, puis définir le nom de ce dernier.

_ Enfin, il est possible de préciser les rôles de base de données accordés à cet utilisateur.

b) En Mode T_SQL :

L’exemple suivant créer un compte utilisateur nommé Utilisateur1 dans la base de données Stagebd, le schéma
par défaut est DBO.

USE [stagebd]

CREATE USER [Utilisateur1] FOR LOGIN [Test] WITH DEFAULT_SCHEMA=[dbo]

GO

2.2.2. Modification :

a) En mode SSMS :

1. Choisir l’utilisateur à modifier.

2. Cliquer sur le bouton droit de la souris et choisir ‘Propriétés ‘.

3. Changer les propriétés voulues et valider par ‘OK’

b) En Mode T_SQL : On utilise la commande ALTER.

Exemples :

L'exemple suivant modifie le nom de l'utilisateur de base de données Uitilisateur1 en U1.

USE stagebd;
ALTER USER Utilisateur1 WITH NAME = U1;
GO
2.2.3. Suppression :

a) En mode SSMS :

1. Choisir l’utilisateur à supprimer.

2. Cliquer sur le bouton droit de la souris et choisir ‘Supprimer ‘.

4
3. une fenêtre de suppression s’affiche Valider par ‘OK’.

b) En Mode T_SQL : On utilise la commande DROP.

Exemple : Supprimer l’utilisateur Utilisateur1 :

Use stagebd
DROP User Utilisateur1
Go
3. Les rôles

3.1. Définition :

Les rôles sont ensembles de permissions, SQL server propose des rôles prédéfinis aussi appelés fixes,
ces rôles fixes sont définis à deux niveaux :

_ Server

_ Base de données

3.2.1. Rô les de serveur ( Fixes ) :

Le tableau suivant décrit les rôles fixes de serveur dans SQL Server :

Rôle Utilisation

Administrateurs d'insertion en bloc Exécute l'instruction BULK INSERT pour une insertion des
Bulkadmin données par bloc.

Créateurs de base de données Dbcreator Permet de Créer et modifier les bases de données.

Administrateurs de disque Diskadmin Permet de Gérer les fichiers sur disque.

Administrateurs de processus Permet de Gère les traitements s'exécutant sur SQL

Processadmin Server.

Administrateurs de la sécurité
Permet de Gérer les connexions d'accès au serveur
Securityadmin

Permet de Configurer les paramètres au niveau du

Administrateurs du serveur Serveradmin
serveur.

Administrateurs de l'installation et de la Permet d’Ajouter/supprimer des serveurs liés et d’exécuter

configuration Setupadmin certaines procédures stockées système

Administrateurs système Sysadmin Exécute n'importe quelle opération sur le serveur.

5
• Ajout d'un membre à un rô le de serveur fixe
a) A l’aide de SSMS :
1. depuis l’explorateur d’objet, se placer sur le nœud Sécurité-rôles du serveur.
2. Dans le volet des informations, cliquez avec le bouton droit sur rôle, puis cliquez sur
Propriétés.
3. Dans l'onglet Général, cliquez sur Ajouter, puis sur les connexions à ajouter.

b) A l’aide de T_SQL :
Syntaxe :
sp_addsrvrolemember 'nom connexion' , 'nom rô le'
Exemple:
Ajout du compte serveur 'Test' au rôle 'sysadmin '
sp_addsrvrolemember 'test', 'sysadmin'
GO
 Suppression d'une connexion d'un rô le de serveur fixe

a) A l'aide de SSMS :
1. depuis l’explorateur d’objet, se placer sur le nœud Sécurité-rôles du serveur.
2. Dans le volet des informations, cliquez avec le bouton droit sur le rôle du serveur à modifier,
Puis cliquez sur Propriétés.
3. Dans l'onglet Général, cliquez sur la connexion à supprimer, puis cliquez sur Supprimer.

b) A l'aide de T_SQL :

6
 sp_dropsrvrolemember 'nom connexion' , 'nom rô le'
Exemple:
Suppression du compte serveur ‘Test’ du rôle ‘sysadmin '

sp_dropsrvrolemember 'test', 'sysadmin'

GO

3.2.2. Rô les de base de données

Les rôles de bases de données permettent toujours de regroupe les différentes autorisations ou refus
d’instruction, il existe des rôles prédéfinis et il possible de définir ses propres rôle.
3.2.2.1. Rô les de base de données fixes 

Rôle Utilisation

créateur par défaut des objets de la base, il possède toutes les autorisations
db_owner
sur la base de données.

db_accessadmin gère les accès : ajoute ou supprime des ID utilisateur.

Permet de gérer rôles, les accès membres des rôles, et les autorisations sur
db_securituadmin
les instructions et les objets de la base de données.

Permet d’ajouter (create), modifier (alter) ou supprimer (delete) des objets de

db_ddladmin
la base de données.

db_backupoperator Permet d’effectuer une sauvegarde de la base de données.

droit de consultation des données de la base (lecture uniquement). Sélectionne

db_datareader
toutes les données de toutes les tables utilisateur dans la base de données.

droit en lecture, écriture des données de la base. Modifier supprimer les

db_datawriter
données de toutes les tables utilisateur dans la base de données.

db_denydatareader Interdit la visualisation des données de la base.

db_denydatawriter Interdit la modification des données contenues dans la base.

• Ajout d'un membre à un rô le de base de données SQL Server :

a) A l'aide de SSMS :

1. Développez Bases de données, puis développez la base de données où figure le rôle.

2. Cliquez sur Rôles.

3. Dans le volet des informations, cliquez avec le bouton droit sur le rôle auquel l'utilisateur va être
ajouté, puis cliquez sur Propriétés.

7
4. Cliquez sur Ajouter, puis sur le ou les utilisateurs à ajouter.

b) A l'aide de T_SQL :
sp_addrolemember 'nom rôle', 'compte sécurité'

compte sécurité :nom d’un utilisateur de base de données ou bien d’un

rôle SQL Server.

Exemple:
Ajout de l’utilisateur 'inf'au rôle 'db_datareader'
USE [stagebd]
GO
sp_addrolemember 'db_datareader', 'inf'
 Suppression d'un compte d'utilisateur d'un rô le fixe de base de données :
a) A l'aide de SSMS :
1. Développez Bases de données, puis développez la base de données où figure le rôle.
2. Cliquez sur Rôles.
3. Dans le volet des informations, cliquez avec le bouton droit sur le rôle auquel appartient le
compte d'utilisateur puis cliquez sur Propriétés.
4. Sélectionnez l'utilisateur à supprimer, puis cliquez sur Supprimer
b) A l'aide de T_SQL :
sp_droprolemember 'nom rôle', 'compte sécurité'

8
 compte sécurité :nom d’un utilisateur de base de données ou bien
d’un rôle SQL Server.
Exemple:
Suppression de l’utilisateur 'inf' du rôle 'db_datareader'
USE [stagebd]
GO
EXEC sp_droprolemember 'db_datareader', 'inf'

3.2.2.2. Rô les de base de données définis par l'utilisateur :

Il est possible de définir ses propres rôles afin de faciliter l’administration des droits à l’intérieur
de la base.
 Création d'un rô le de base de données SQL Server :
a) A l'aide de SSMS :
1. Développez Bases de données, puis développez la base de données dans laquelle vous
voulez créer un rôle.
2. Cliquez avec le bouton droit sur Rôles, puis cliquez sur Nouveau rôle de base de données .
3. Dans la zone Nom, entrez le nom du nouveau rôle.
4. Le cas échéant, cliquez sur Ajouter pour ajouter des membres à la liste Rôle standard ,
puis cliquez sur un ou plusieurs utilisateurs à ajouter.

9
 b) A l'aide de T_SQL :
Syntaxe :
create role nomrole authorization propriétaire
Exemple:
Création d’un rôle nommé roleTest pour l’utilisateur inf.

USE [stagebd]
create role roletest authorization inf

Go

 Suppression d'un rôle de base de données défini par l'utilisateur

ƒ il est Impossible de supprimer un rôle s'il possède des membres. Il faut d'abord supprimer
tous les membres du rôle avant de supprimer celui-ci
a) A l'aide de SSMS :

1.Développez Bases de données, puis développez la base de données où figure le rôle.

2. Cliquez sur Rôles.

3.. Dans le volet des informations, cliquez avec le bouton droit sur le rôle puis cliquez sur
Propriétés.

4. Si le rôle possède des utilisateurs, supprimer les tous avant de supprimer le rôle

5. cliquez sur ok pour fermer la fenêtre

6. Dans le volet des informations, cliquez avec le bouton droit sur le rôle puis cliquez sur
Supprimer.

7. Confirmez la suppression.

b) A l'aide de T_SQL :
USE [stagebd]
GO
Drop ROLE [roleTest]

Go

10