MSDE Altre istruzioni per l'uso (parte 9) - Gestire Sicurezza e Accessi
a cura di Sabrina Cosolo (requisiti: Livello intemedio di conoscenza generale)

Creare un Utente su SQL Server e assegnargli i diritti per usare un database:
La gestione della sicurezza di SQL Server è materia complessa che non può certo essere trattata in un singolo articolo perciò ci limiteremo all'ABC.
SQL server è un motore Database che non è nato alla Microsoft, ma deriva dal motore SYBASE acquistato da Microsoft negli anni novanta e poi evolutosi in maniera indipendente dalla sua origine.
Ogni RDBMS ha la necessità di gestire la sicurezza dei dati a vari livelli, ovvero non tutti coloro che hanno accesso al server Windows devono per forza essere in grado di vedere tutto ciò che è in SQL Server, perciò SQL Server ha una propria gestione della sicurezza con la possibilità di gestire utenti e assegnare loro ruoli e permessi all'interno del server dati. Ovviamente, essendo di proprietà di Microsoft, gli è stata aggiunta una seconda possibilità di dare accesso agli utenti: quella che viene definita Windows Security e che usa le cosiddette Trusted connection per stabilire i diritti degli utenti che accedono al server dati utilizzando i gruppi di utenti o gli utenti di un dominio windows; questo significa che la sicurezza Windows funziona solo su sistemi NT, 2000, XP e 2003.
Quando il server dati viene installato, se non diversamente specificato, SQL Server viene attivato per la sola Sicurezza Windows e gli unici ad avere accesso al server dati come System Administrator sono gli utenti appartenenti al gruppo Builtin/Administrator della macchina su cui SQL Server è installato. Altrimenti, specificando che si vuole utilizzare un sistema di sicurezza misto, viene attivato anche l'utente SA (fino all'SP2 senza Password, dall' SP3 con password obbligatoria oppure vuota su richiesta).

Detto questo, su tutti i manuali d'uso è specificato che fare accedere i normali utenti come System Administrator o assegnarli tutti al gruppo dei Builtin/Administrators della macchina server è una procedura sconsigliabile; ma allora che cosa possiamo fare per generare un utente, farlo connettere al nostro server, farlo lavorare con il nostro database e fargli inserire, modificare, cancellare dati in santa pace?

Qualcosa di veramente semplice...

  sp_addlogin @loginame='Pippo', @passwd='pippo'
  sp_grantlogin '4unb03\Pippo'

Come potete vedere abbiamo utilizzato 2 stored procedure di sistema per fare due diverse operazioni:

  sp_addlogin @loginame='nomeutente', @passwd='password'

ci ha permesso di creare un nuovo utente per l'accesso con Sicurezza a livello SQL; 

  sp_grantlogin 'domainuser'

ci ha permesso di dare ad un utente (o un gruppo di utenti) del dominio 4unb03 (il mio PC) l'accesso al server dati

A questo punto il nostro utente Pippo e il nostroutente di dominio 4unb03\Pippo possono accedere al server dati (e quindi se effettuate una connessione da VB non vi sarà errore), però entrambi hanno un ruolo di guest su tutti i database e non possono fare praticamente nulla (poverini!); quindi adesso dobbiamo assegnare a ciascuno di loro un ruolo a livello di server se necessario ed un ruolo a livello di database, per far sì che possano vedere e modificare i dati del database.

  sp_grantdbaccess 'Pippo'
  sp_addrolemember 'db_datareader', 'Pippo'
  sp_addrolemember 'db_datawriter', 'Pippo'

Abbiamo eseguito altre due stored procedure - è sottinteso, ma forse importante ricordare, che le procedure suddette vanno eseguite da SA o da un Builtin/Administrator connesso al database Musicbox :
la prima, sp_grantdbaccess, dà a Pippo il permesso di vedere il database Musicbox
la seconda, sp_addrolemember, assegna Pippo a due dei ruoli predefiniti predisposti sui Database di SQL Server, che sono esattamente:

Quelli che noi tutti useremo di più saranno: db_owner, db_datareader e db_datawriter.
Ovviamente è possibile anche cancellare gli utenti dal database e negare loro l'accesso al server.
Fate attenzione a non cancellare gli accessi del Builtin/Administrator, se non avete attivato l'accesso con sicurezza SQL, perché SA non potrà essere usato per accedere al server e il server diverrà virtualmente inaccessibile.

Soluzione? Reinstallare SQL Server (Parlo per esperienza, l'ho fatto proprio al corso di Amministrazione server di Microsoft).

Ma vediamo come si cancellano i nostri user:

  sp_revokedbaccess 'Pippo'
  sp_droplogin 'Pippo'
  sp_revokelogin [4unb03\Pippo]

Altre tre stored procedure di sistema che sono l'opposto delle procedure usate per dare accesso agli utenti.

Anche per questi comandi SQL si può utilizzare OSQL.EXE (vedi, anche per i parametri).

La sintassi per chiamare un comando SQL usando OSQL è la seguente:

  osql -S Localhost -U SA -P as -i usersql.sql

un possibile file di input da utilizzare per realizzare uno degli esempi usersql.sql è un file di testo contenente il comando da eseguire che è leggermente diverso da quello sopra descritto, riporto la sintassi per l'assegnazione dei diritti di accesso ai nostri utenti: 

  USE MusicBox
  GO
 
  exec sp_addlogin @loginame='Pippo', @passwd='pippo'
  exec sp_grantlogin '4unb03\Pippo'
  exec sp_grantdbaccess 'Pippo'
  exec sp_addrolemember 'db_datareader', 'Pippo'
  exec sp_addrolemember 'db_datawriter', 'Pippo'
 
  GO
 
  QUIT

Il comando USE MUSICBOX serve a dire al server che dobbiamo lavorare sul Database Musicbox (database di esempio creato nel precedente articolo), il comando GO esegue i comandi richiesti, il comando QUIT esce da OSQL.EXE che altrimenti si comporta come un interprete comandi e rimane attivo all'interno della finestra console.

La sintassi da usare con OSQL per trasportarvi tutti gli script da noi generati in questa pagina è la seguente: 

  USE Musicbox
  GO 
 
  script da eseguire
 
  GO
 
  QUIT
<<< Salvare/Trasportare Database