Come generare script di creazione utenti e ruoli per un database SQL Server

Introduzione

È stata ricevuta dal team di sviluppo una richiesta per la copia di uno dei database di produzione su sviluppo, allo scopo di simulare dei test in tempo reale. Una volta che il backup del database di produzione è stato ripristinato sull’istanza di sviluppo rimpiazzando la vecchia copia, i precedenti user del db di sviluppo sono sostituiti da quelli nuovi di produzione. Però noi abbiamo ancora bisogno dei vecchi login per la connessione al database di sviluppo.

Come possiamo fare per avere una copia di questi login con i loro permessi, prima di sostituire il vecchio database con il nuovo, per poterli poi ripristinare nuovamente?

Metodi di produzione degli script di creazione utenti e permessi

Per essere in grado di connettersi a SQL Server, è necessario avere accesso al server attraverso un metodo di autenticazione di SQL Server o di Windows. Un utente di db è associato ad un login esistente tramite la proprietà SID dei login e degli user. Questa associazione funziona se i valori SID sono identici. Un utente orfano è un utente senza un corrispondente SQL login. Un’eccezione a questa regola è rappresentata dai cosidetti “contained database”, dove gli utenti sono in grado di connettersi al database senza l’autenticazione di un login a livello di server.

I login ed i permessi di SQL Server sono archiviati nelle tabelle di sistema del catalogo di sicurezza nel database master. Per elencare i login ed i ruoli assegnati, è necessario interrogare queste tabelle di sistema.

Nel nostro caso, una volta che il database di produzione è stato ripristinato sull’ambiente di sviluppo, i vecchi utenti del database sono già esistenti come login, ma non sono associati al database visto che questo è stato sostituito da quello nuovo (questo nel caso in cui gli utenti dell’ambiente di sviluppo siano differenti da quelli di produzione).

Per mappare i permessi per gli utenti, abbiamo le seguenti possibilità:

  1. Associare manualmente gli utenti uno ad uno, operazione che richiede parecchio tempo.
  2. Usare T-SQL per generare questi utenti e permessi dal precedente database prima di sovrascriverlo, poi eseguire nuovamente gli script per fare nuovamente l’associazione, dopo aver ribaltato il backup di produzione su sviluppo.

Lo script consiste di due parti; la prima serve per elencare tutti gli utenti del database, eccetto quelli predefiniti, con l’istruzione CREATE USER: 

SELECT 'CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']' AS '--Database Users Creation--'
FROM sys.database_principals
WHERE Type IN (
		'U'
		,'S'
		)
	AND NAME NOT IN (
		'dbo'
		,'guest'
		,'sys'
		,'INFORMATION_SCHEMA'
		)

Il risultato di questa query appare come:

Il secondo step consiste nel generare l’appartenenza ai ruoli del database per ogni utente, come istruzione EXEC sp_AddRoleMember: 

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

Eseguendo la seconda query, si ottiene il risultato sottostante:

Adesso abbiamo lo script per creare ciascun utente a livello di database associarlo al corrispondente login e dare gli accessi aggiungendolo ai ruoli del database. L’ultimo passo è eseguire lo script al database dopo che è stato ripristinato.

Se ti imbatti nell’errore sottostante in fase di mappatura degli utenti:

Error 15023: User or role ‘%s’ already exists in the current database.

Allora vuol dire che c’è uno degli utenti sviluppo che ha lo stesso nome di uno di produzione ma con un diverso SID, visto che sono stati creati su server differenti. Per risolvere questo problema si può utilizzare la sp_change_users_login nel modo seguente: 

EXEC sp_change_users_login 'Auto_Fix'
	,'YourUser'
GO

Come si può vedere, questo metodo richiede un grosso sforzo per generare gli script T-SQL, e potrebbe non funzionare nel caso di autenticazione SQL Server a causa di due differenti SID per lo stesso nome utente generati su istanze diverse di SQL Server.

Per risolvere questo problema e generare script T-SQL per tutti gli utenti con i propri permessi e fornire l’autenticazione di SQL Server per gli utenti con il SID corretto, si può usare ApexSQL Script, un tool di terze parti per scrivere gli oggetti di database ed i dati, fornito da ApexSQL.

Usare ApexSQL Script

ApexSQL Script può essere facilmente impiegato per produrre gli script di creazione degli utenti con i relativi permessi, tramite i seguenti passi:

  1. Avviare ApexSQL Script
  2. Nel tab Select databases, specificare il server che contiene il database del quale produrre gli script di creazione utenti ed il tipo di autenticazione da utilizzare per la connessione a SQL Server. Se si specifica autenticazione di SQL Server, devono anche essere indicati username e password. Alla fine cliccare Connect.

  3. L’elenco di tutti i database contenuti sul server viene mostrato nella griglia di destra, Scegliere il database per il quale produrre gli script; nel nostro esempio si tratta di SQLShackDemo

  4. Nel tab Structure (vedere sotto), scegliere Permissions per generare gli script degli utenti con i loro permessi.
    Si può selezionare Logins with Users se invece si vogliono generare anche gli script per la creazione dei login a livello di server per gli utenti del db. Nel nostro esempio, si presuppone che i login siano già esistenti sul server di sviluppo, ma è necessario fare il backup degli utenti del database prima di sostituire il precedente db di sviluppo con quello nuovo di produzione.

  5. Cliccare Open.

  6. I componenti del database selezionati sono elencati; da lì si può scegliere per quali utenti si vogliono produrre gli script. Per avere gli script degli utenti con l’appartenenza ai ruoli, andremo a selezionare tutti gli utenti e tutti i ruoli come nell’esempio qui riportato.

  7. Cliccare Script dal menu Home.

  8. Nella finestra di dialogo Script, scegliere T-SQL come tipo di output e cliccare Next.

  9. Nella finestra Dependencies cliccare Next.

  10. Nella finestra Output file, dal menu a discesa Action, scegliere create and write to file. In questo modo si può avere la possibilità di salvare lo script in uno specifico percorso oppure aprirlo in un editor. Scegliere Open script in editor e cliccare Create.

Lo script risultante contiene le istruzioni per creare gli utenti selezionati ed aggiungerli ai ruoli di database che specificano i loro permessi. Questo script può essere facilmente eseguito

Se esiste un utente con autenticazione SQL Server in comune tra sviluppo e produzione, si può creare il login con il SID originale, scegliendo Logins with Users dalla finestra Structure ed il risultato sarà:

Conclusioni

Come si può facilmente vedere, ApexSQL Script è un semplice strumento che ci può aiutare in questa circostanza. Si può utilizzare per creare script degli utenti di un database con le loro appartenenze ai rispettivi ruoli, in pochi passi senza il bisogno di essere familiari con le tabelle dei database di sistema, le SPs ed il modo di utilizzarle. Questo tool permette anche superare il problema della diversità del SID per differenti istanze, in caso di autenticazione di tipo SQL Server, tramite la generazione di script per la creazione di utenti con il proprio SID direttamente dal server di origine.

Collegamenti Utili

Author: Ahmad Yaseen

 

Related posts:

  1. Usare le regular expressions (RegEx) in SQL Server per generare dati di test casuali
  2. Tecniche per import ed export massivi in SQL Server
July 25, 2017