Come verificare i ruoli utente in SQL Server

Netwrix Auditor for SQL Server

1. Apri Netwrix Auditor e vai su Reports -> Predefined -> SQL Server - State-in-Time -> Account Permissions in SQL Server.
2. Specificare i seguenti filtri:
   • Nel filtro dell'User account, digita il nome completo dell'utente (come MILKYWAY/TomSimpson).
   • Nel filtro del tipo di Object type, scegli Server Instance, Database.
3. Fai clic su View per creare un report chiaro sui permessi effettivi dell'utente.

Soluzione nativa

Elenco dei ruoli SQL Server per un utente

1. Avvia Microsoft SQL Server Management Studio (MSSMS).
2. Nel menu File, fare clic su Connect Object Explorer.
3. Nella finestra di dialogo Connect to Server, specificare le seguenti impostazioni:
   • Nella casella di riepilogo Server type, selezionare Database Engine.
   • Nel campo di testo Server name, digitare il nome del server cluster SQL.
   • Nella casella di elenco Authentication seleziona il tuo metodo di SQL Server Authenticationmethod e specifica le credenziali da utilizzare. Se non vuoi reinserire la password ogni volta che ti connetti al server, seleziona l'opzione Remember password.
4. Clicca Connect.
5. Fai clic su Execute (o premi il tasto F5 ).
6. Dopo la connessione, fai clic su New Query e incolla lo script seguente nel campo di query:

      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'
      

• Rivedi l'elenco dei ruoli a livello di server e dei principali (nomi dei membri) nei risultati dell'esecuzione della query:

Interrogazione dei ruoli del database in SQL Server per un utente

1. Avvia Microsoft SQL Server Management Studio (MSSMS).
2. Nel menu File, fare clic su Connect Object Explorer.
3. Nella finestra di dialogo Connect to Server, specificare le seguenti impostazioni:
   • Nella casella di riepilogo Server type, selezionare Database Engine.
   • Nel campo di testo Server name, digitare il nome del server cluster SQL.
   • Nella casella di elenco Authentication, scegli il tuo metodo di SQL Server Authentication e specifica le credenziali da utilizzare. Se non vuoi reinserire la password ogni volta che ti connetti al server, seleziona Remember password.
4. Clicca Connect.
5. Clicca Execute (o premi il tasto F5 ).
6. Dopo la connessione, seleziona il Database di cui hai bisogno per interrogare i ruoli utente.
7. Clicca su Nuova Query e incolla il seguente script nel campo della query:

      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'
      

• Esaminate l'elenco dei ruoli a livello di server e dei principali (nomi dei membri) nei risultati dell'esecuzione della query:
  

Scopri come verificare i ruoli degli utenti in SQL Server senza una singola query.

Microsoft SQL Server offre ruoli per aiutare gli amministratori di database a gestire i permessi ai dati strutturati. I ruoli a livello di server, come suggerisce il nome, concedono l'accesso a livello di server, similmente ai gruppi nel mondo Windows. Ogni database SQL può anche avere i suoi permessi e ruoli unici.

Per mantenere la sicurezza e conformarsi a molte normative, inclusi PCI DSS e HIPAA, è necessario conoscere tutti i ruoli di server e database assegnati a ciascun utente. A causa della complessità coinvolta, è un compito difficile se si dispone solo di strumenti nativi.

Per cominciare, le impostazioni a livello di server, come i ruoli del server, i permessi, le credenziali degli utenti e le dipendenze, sono memorizzate nel database master. Utilizzando la vista di sistema sys.server_principals.

• Per elencare utenti e ruoli del server in SQL Server, puoi interrogare viste di sistema come sys.server_principals.
• Per elencare utenti e ruoli per i database in SQL Server, è possibile interrogare viste di sistema come sys.database_principals.

Mentre le stored procedure possono assisterti nella gestione di aree del server, dovrai utilizzare le query per costruire report personalizzati (ad esempio, uno che corrisponda a diverse tabelle per nomi di colonne specifici). Ad esempio, le informazioni sull'appartenenza ai ruoli a livello di server sono memorizzate nella vista di sistema server_role_members del database master. Poiché gli ID dei principali sono collegati, puoi ottenere un riassunto dei ruoli utente di SQL Server con una query unendo sys.server_principals con master.sys.server_role_members in base al numero ID. Sebbene gli utenti possano visualizzare la loro appartenenza ai ruoli del server e l'ID principale di ciascun membro dei ruoli fissi del server, ricorda che la visualizzazione dell'appartenenza completa ai ruoli del server richiede permessi aggiuntivi o l'appartenenza al ruolo fisso del server security admin.

Per raccogliere informazioni a livello di database, è necessario interrogare i ruoli del database SQL Server su ogni database individualmente, il che può richiedere molto tempo. Inoltre, i ruoli possono essere nidificati: gli utenti del database, i ruoli dell'applicazione e altri ruoli del database possono essere membri di un ruolo del database.

In sintesi, ottenere informazioni complete sui ruoli attuali degli utenti con gli strumenti nativi può essere complicato e estremamente faticoso. Con Netwrix Auditor, invece, puoi ottenere informazioni dettagliate su chi detiene quali ruoli di server e database in un formato leggibile con pochi clic. I report collegati inclusi nel prodotto consentono ai tuoi specialisti di setacciare rapidamente le complessità dell'appartenenza ai ruoli utente annidati in un intero server, semplificando le indagini. Ottieni tutti i dettagli critici di cui hai bisogno: un elenco di ogni oggetto a cui l'utente ha accesso, con il suo percorso e tipo di oggetto, i permessi che sono stati concessi, come sono stati concessi (direttamente, tramite appartenenza al ruolo, ecc.), e se sono espliciti o ereditati. Puoi analizzare i permessi da diverse prospettive, inclusi il livello dell'account, dell'oggetto e del server. Tutte queste informazioni sono presentate e disponibili senza il fastidio di scrivere script, esportare informazioni in Excel e setacciarle manualmente.