Comment vérifier les rôles d'utilisateur dans SQL Server

Netwrix Auditor for SQL Server

1. Ouvrez Netwrix Auditor et naviguez vers Rapports -> Prédéfinis -> SQL Server - État dans le temps -> Permissions de compte dans SQL Server.
2. Spécifiez les filtres suivants :
   • Dans le filtre User account, tapez le nom complet de l'utilisateur (tel que MILKYWAY/TomSimpson).
   • Dans le filtre Object type , choisissez Server Instance, Database.
3. Cliquez View pour créer un rapport clair sur les permissions effectives de l'utilisateur.

Solution native

Listage des rôles SQL Server pour un utilisateur

1. Commencez Microsoft SQL Server Management Studio (MSSMS).
2. Dans le menu File, cliquez sur Connect Object Explorer.
3. Dans la boîte de dialogue Connect to Server, spécifiez les paramètres suivants :
   • Dans la liste déroulante Server type, sélectionnez Database Engine.
   • Dans la zone de texte Server name, saisissez le nom du serveur de cluster SQL.
   • Dans la liste déroulante Authentication, choisissez votre SQL Server Authenticationmethod et spécifiez les identifiants à utiliser. Si vous ne souhaitez pas retaper le mot de passe à chaque connexion au serveur, cochez Remember password.
4. Cliquez Connect.
5. Cliquez sur Exécuter (ou appuyez sur la touche F5 ).
6. Lors de la connexion, cliquez sur New Query et collez le script suivant dans le champ de requête :

      select r.name as Role, m.name as Principal

from

    master.sys.server_role_members rm

    inner join

    master.sys.server_principals r on r.principal_id = rm.role_principal_id and r.type = 'R'

    inner join

    master.sys.server_principals m on m.principal_id = rm.member_principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• Examinez la liste des rôles au niveau du serveur et des principaux (noms des membres) dans les résultats de l'exécution de la requête :

Interrogation des rôles de base de données dans SQL Server pour un utilisateur

1. Commencez Microsoft SQL Server Management Studio (MSSMS).
2. Dans le menu File, cliquez sur Connect Object Explorer.
3. Dans la boîte de dialogue Connect to Server, spécifiez les paramètres suivants :
   • Dans la liste déroulante Server type, sélectionnez Database Engine.
   • Dans la zone de texte Server name, saisissez le nom du serveur de cluster SQL.
   • Dans la liste déroulante Authentication, choisissez votre méthode d'authentification SQL Server et spécifiez les identifiants à utiliser. Si vous ne souhaitez pas ressaisir le mot de passe à chaque fois que vous vous connectez au serveur, cochez l'option Remember password.
4. Cliquez Connect.
5. Cliquez sur Execute (ou appuyez sur la touche F5 ).
6. Lors de la connexion, sélectionnez la Database que vous devez interroger pour les rôles d'utilisateur.
7. Cliquez sur Nouvelle requête et collez le script suivant dans le champ de requête :

      SELECT r.name role_principal_name, m.name AS member_principal_name

FROM sys.database_role_members rm 

JOIN sys.database_principals r 

    ON rm.role_principal_id = r.principal_id

JOIN sys.database_principals m 

    ON rm.member_principal_id = m.principal_id

where m.name = 'MILKYWAY\TomSimpson'
      

• Examinez la liste des rôles au niveau du serveur et des principaux (noms des membres) dans les résultats de l'exécution de la requête :
  

Découvrez comment vérifier les rôles des utilisateurs dans SQL Server sans une seule requête.

Microsoft SQL Server fournit des rôles pour aider les administrateurs de bases de données à gérer les permissions des données structurées. Les rôles au niveau du serveur, comme leur nom l'indique, accordent un accès à l'échelle du serveur, semblable aux groupes dans l'univers Windows. Chaque base de données SQL peut également avoir ses permissions et rôles uniques.

Pour maintenir la sécurité et se conformer à de nombreuses réglementations, y compris PCI DSS et HIPAA, vous devez connaître tous les rôles de serveur et de base de données attribués à chaque utilisateur. En raison de la complexité impliquée, c’est un travail difficile si vous ne disposez que d'outils natifs.

Pour commencer, les paramètres au niveau du serveur, tels que les rôles de serveur, les permissions, les informations d'identification des utilisateurs et les dépendances, sont stockés dans la base de données principale. En utilisant la vue système sys.server_principals .

• Pour lister les utilisateurs et les rôles de serveur dans SQL Server, vous pouvez interroger des vues système comme sys.server_principals.
• Pour lister les utilisateurs et les rôles pour les bases de données dans SQL Server, vous pouvez interroger des vues système comme sys.database_principals.

Bien que les procédures stockées puissent vous aider à gérer des domaines du serveur, vous devrez utiliser des requêtes pour créer des rapports personnalisés (par exemple, un qui correspond à plusieurs tables par noms de colonnes spécifiques). Par exemple, les informations sur l'appartenance aux rôles au niveau du serveur sont stockées dans la vue système server_role_members de la base de données master. Comme les identifiants des principaux sont liés, vous pouvez obtenir un résumé des rôles d'utilisateur de SQL Server avec une requête en joignant sys.server_principals avec master.sys.server_role_members en fonction du numéro d'ID. Bien que les utilisateurs puissent voir leur appartenance aux rôles de serveur et l'ID principal de chaque membre des rôles fixes du serveur, n'oubliez pas que la visualisation de toute l'appartenance aux rôles de serveur nécessite des permissions supplémentaires ou l'appartenance au rôle fixe de serveur security admin.

Pour collecter des informations au niveau de la base de données, vous devez interroger les rôles de base de données SQL Server sur chaque base de données individuellement, ce qui peut prendre du temps. De plus, les rôles peuvent être imbriqués : les utilisateurs de la base de données, les rôles d'application et d'autres rôles de base de données peuvent être membres d'un rôle de base de données.

En résumé, obtenir des informations complètes sur les rôles actuels des utilisateurs avec les outils natifs peut être compliqué et carrément épuisant. Avec Netwrix Auditor, en revanche, vous pouvez obtenir des informations détaillées sur qui détient quels rôles de serveur et de base de données dans un format lisible en quelques clics. Les rapports liés inclus dans le produit permettent à vos spécialistes de filtrer rapidement les complications de l'appartenance aux rôles d'utilisateur imbriqués sur un serveur entier, simplifiant les enquêtes. Vous obtenez tous les détails critiques dont vous avez besoin : une liste de chaque objet auquel l'utilisateur a accès, avec son chemin et son type d'objet, les permissions qui ont été accordées, comment elles ont été accordées (directement, par l'appartenance à un rôle, etc.), et si elles sont explicites ou héritées. Vous pouvez analyser les permissions sous différents angles, y compris les niveaux de compte, d'objet et de serveur. Toutes ces informations sont présentées et disponibles sans la peine d'écrire des scripts, d'exporter des informations dans Excel et de les trier manuellement.