APSQL

Créer des connexions avec les objets SMO et le PowerShell

Suite à l'introduction à SQLPS puis le travail avec les objets SMO pour définir une nouvelle table, l'objectif ici est de mettre en avant les avantages à travailler directement en PowerShell pour réaliser certaines tâches administratives.

L'objectif va consister ici à lire les utilisateurs définis localement et à définir des connexions SQL Server correspondant à ces utilisateurs locaux.

Etape 1 : Définir un lecteur personnaliser

Le positionnement sur la bonne instance SQL Serveur peux produire une invite de commande longue et du coup la lecture des instructions PowerShell est moins aisée.

La création de lecteur personnalisé permet également de gagner en efficacité car il est ainsi possible de se positionner rapidement sur un élément.

Par exemple pour se positionner sur l'instance SQL Server par défaut s'exécutant sur le serveur local, le lecteur MSSQLlocal sera défini de la façon suivante:(dans cet exemple l'ordinateur local se nomme ARAVIS)

New-PSDrive -Name MSSQLlocal -PSProvider SQLServer -Root SQLSERVER:\SQL\RESEAU-6B81DE5F\Default\

Les trois paramètres de cette instructions sont:

  • Name : permet de définir le nom du nouveau lecteur
  • PSProvider : permet de spécifier le fournisseuur PowerShell qui aura la charge de gérer ce lecteur
  • ROOT : représente le chemin d'accès au lecteur à partir de la racine

Pour se positionner sur le nouveau lecteur, l'instruction Set-Location sera utilisé de la façon suivante:

Set-Location  MSSQLlocal:\

Comme pour toute les éléments PowerShell, les applets de commande Get-PSDrive et Remove-PSDrive permettent de connaitre la liste des lecteurs définis ou bien de supprimer la définition de un ou plusieurs lecteurs.

Etape 2 : lister les comptes d'utilisateurs locaux

Pour obtenir la liste des utilisateurs locaux, il est possible de produire le script PowerShell suivant:

$lesUtilisateurs=[ADSI]"WinNT://."
$lesUtilisateurs.psbase.children|where{$_.psbase.SchemaClassName -eq 'user'}|ForEach{$_.Name}

L'objectif étant ici de se centrer sur des scripts liés à SQL Server, l'analyse de ce script ne sera pas fournie. Toutefois, pour localiser ce type de script, il est possible de faire appel au script center du technet : http://technet.microsoft.com/en-us/scriptcenter/default.aspx

Etape 3 : Créer les connexions

Il s'agit maintenant à partir de la liste des utilisateurs de définir les connexions SQL Server associées. Pour cette mise en place nous allons travailler avec l'objet SMO Microsoft.SqlServer.Management.Smo.Login. Cet objet nécessite la valorisation des propriétés suivantes:

  • Parent : afin de préciser le serveur concernée par la création de connexion
  • Name : pour préciser le nom complet de la connexion. Dans la cas d'une authentification Windows le nom doit être au format nomDomainre\nomUtilisateur ou bien nomOrdinateur\nomUtilisateur pour un compte local.
  • LoginType: afin de préciser le type de connexion (authentification SQL Server, authentification Windows et pour ce dernier cas est ce un groupe ou bien un utilisateur?). Ce type ne peut être valorisé qu'après avoir précisé le nom (Name) et le serveur(Parent) pour la connexion (login).

Enfin la création de la connexion est effectuée à l'aide de la méthode Create(). Il est également possible de récupérer (dans un fichier ou simplement pour affichage) l'instruction SQL correspondante en appelant la méthode Script().

Le script final

# 1  Récupérer le nom de l'ordinateur
$nomOrdinateur=(get-item env:\computerName).value
# 2  Se connecter au serveur
$server=New-Object Microsoft.SqlServer.Management.Smo.Server;
# 3 Récupérer les utilisateurs locaux
$lesUtilisateurs=[ADSI]"WinNT://.";
$lesUtilisateurs.psbase.children|where{$_.psbase.SchemaClassName -eq 'user'}|ForEach{
# 4 Définir une nouvelle connexion
$login=New-Object Microsoft.SqlServer.Management.Smo.Login;
$login.Parent=$server;
$login.Name=$nomOrdinateur+'\'+$_.Name;
$login.Logintype=[Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser;
# 5 Demander sa création
$login.Create();
}