Powershell es una magnífica herramienta para scripting y administración de servidores y es por esto que cada vez más productos van incorporando sus propios CMDLets para realizar las tareas más comunes. SQL Server no es una excepción y en la versión 2012 incorpora modulos con una buena variedad de comandos que nos permiten realizar tareas de administración sobre el motor de bases de datos, los servicios de análisis, de integración, etc.. En esta versión todavía existe el ejecutable sqlps.exe pero se verá depreciado en futuras versiones.Para poder explotar estas funcionalidades desde Powershell, nuestro sistema debe cumplir ciertos requisitos:
· SQL Server 2012. Es conveniente dejar claro es que este script esta orientado a la utilización de los módulos Powershell de SQL Server 2012
· Políticas de ejecución. La configuración de las políticas debe estar definida y ser suficiente como para permitir la ejecución de comandos. En Windows 2012, por ejemplo, todos los ámbitos de ejecución se encuentran deshabilitados por defecto (Undefined):
Podemos corregir esta situación habilitando estableciendo una política de ejecución para alguno de los ámbitos. Vea más sobre Políticas de Ejecución Para las tareas que vamos a realizar en esta entrada podemos ejecutar la siguiente sentencia:
set-executionpolicy RemoteSigned -scope CurrentUser
· Módulo SQLPS, nos va a permitir navegar los servicio añadiendo una unidad Powershell (PSDrive) para SQLServer a través del cual vamos a alcanzar las instancias de Analysis Services. Además tiene añade varios comandos para administrar el motor de base de datos.
· Módulo SQLASCMDLets, en el que se encuentran los comandos relacionados con las operaciones que podemos realizar sobre instancias de Analysis Services
Import-module SQLASCMDLETS
Por ahora contamos con 11 comandos con los que podemos realizar tareas de administración como realizar backups, procesar un cubo, dimensión o partición o incluso hacer un merge entre particiones. Para extraer la lista de comandos que existen en el módulo escribimos la sentencia:
Get-command –module SQLASCMDLETS
 |
En esta entrada está enfocada en la navegación de objetos de Analysis Services y la aplicación del comando Add-RoleMember
Generando el Script
El objetivo del script que vamos a ver en esta entrada es añadir una cuenta de usuario a un determinado rol de base de datos, que puede existir en varias. Es decir, podemos imaginar que existan varias bases de datos con distintos cubo, pero que los roles que hay configurados en cada una corresponden a departamentos, por ejemplo. Lo lógico sería añadir un grupo de Active Directory y gestionar desde allí la inserción de nuevos usuarios de forma que se aplique automáticamente en el cubo, pero ¿si no es así?. O simplemente porque mola hacerlo con Powershell J
Vamos a escribir algunas sentencias que van a realizar este trabajo en una determinada instancia de Analysis Services.
Lo primero que debemos hacer, tras habilitar la ejecución de comandos y scripts en Powershell, es importar los módulos correspondientes. Como vimos antes:
Import-Module sqlps Import-Module sqlascmdlets
El siguiente paso será obtener el host Analysis Services, hay que tener en cuenta que contamos con el host HTTP aunque no tengamos el IIS configurado para acceder, el servicio existe. Podemos obtener una lista de Hosts eliminando los que sean HTTP:
$ASObj = dir sqlserver:sqlas | Where-Object { $_.IsHttpHost -eq $false }
Este objeto tiene una colección de las instancias de Analysis Services instaladas
$ASObj.AnalysisServers
Si tenemos más de una instancia instalada, podríamos recorrerla o pedir al usuario que introduzca el nombre de instancia sobre el que quiere operar. En mi caso he optado por la segunda opción.
$ASInstanceName="InstanciaDesarrollo"
$ASInstance = $ASObj.AnalysisServers | Where-Object {$_.name -eq $ASInstanceName}
Con este objeto tendremos acceso a las bases de datos, y algunas propiedades y colecciones muy interesantes, como los roles de servidor (Administradores) y sus miembros, las bases de datos, etc… A nosotros nos interesa recorrer las bases de datos para acceder a sus roles:
Para esto vamos a utilizar un bucle ForEach para recorrer todas las bases de datos en busca de un rol determinado en todas ellas, y si existe, añadimos el usuario utilizando el cmdlet Add-RoleMember.
#Recorre todas las bases de datos utilizando el filtro generado en el paso anterior
$ASRolName = "Comercial"
ForEach ($DB in $ASInstance.Databases | Where-Object {$_.name -like $ASDatabaseName})
{
write-host "Accediendo a la base de datos $DB"
if ($DB.Roles.Count -ne 0)
{
#Agrega el usuario al rol que ha introducido
ForEach ($rol in $DB.Roles)
{
if ($rol.name -ine $ASRolName)
{
write-host " * rol [$rol] descartado" -fore gray
}
else
{
$rol | Add-RoleMember -MemberName $ASMemberName
write-host " * Usuario [$ASMemberName] agregado al rol [$rol] " -fore Green
}
}
}
else
{
Write-Host " * No existen roles en la base de datos $DB"
}
}
Si os fijáis, utilizamos el pipeline de $rol para implementar el comando Add-RoleMember y pasando como parámetro únicamente el nombre de usuario que va a formar parte del rol.
Darle la vuelta al script para que en lugar de añadir un miembro lo elimine es sumamente sencillo, sólo habría que utilizar el comando Remove-RoleMember
Script completo
Con el resumen de lo más importante, les dejo el script completo que pueden descargar desde la Galería de TechNet:
#Requires -version 2.0
#Requires -modules sqlps,sqlascmdlets
Clear-Host
function load_module($name)
#Intenta cargar el modulos especificado como parametro si no se encuentra ya cargado
{
if ((Get-Module $name).count -eq 0)
{
if (Get-Module -ListAvailable | Where-Object { $_.name -eq $name })
{
Import-Module $name
return $true
}
else
{
return $false
}
}
else
{
return $true
}
}
function CheckUser
#Comprueba la existencia del usuario en el dominio
{
Param ([Parameter(Position=0,
HelpMessage = "Usuario para comprobar su existencia.")]
[string]$user)
if ($user -ilike "**")
{
$domain= $user.Split("")[0]
$usr = $user.Split("")[1]
}
elseif ($user -ilike "*@*")
{
$domain= $user.Split("@")[1]
$usr = $user.Split("@")[0]
}
else
{
$domain= $env:USERDOMAIN
$usr = $user
}
if ($user -eq "")
{
Return $domain + "" + $env:USERNAME
}
$ADSIQuery = [adsisearcher] "(samaccountname=$usr)"
$UserExists = $ADSIQuery.FindOne()
if ($UserExists.count -eq 0)
{
Throw "El usuario $ASMemberName no existe"
}
else
{
if ($domain -ilike "*.*")
{
Return $domain.split(".")[0] + "" + $usr
}
else
{
Return $domain + "" + $usr
}
}
}
Write-host "***************************************************************************" -fore Yellow
Write-host "Este script le permite agregar una cuenta de usuario como miembro de un rol" -fore Yellow
Write-host "en una o todas las bases de datos de una instancia OLAP en este servidor " -fore Yellow
Write-host "//Victor M Garcia Sanchez (@atharky)" -fore yellow
Write-host "***************************************************************************" -fore Yellow
#Comienza el trabajo!
Try
{
if (load_module "sqlps" -and load_module "sqlascmdlets")
{
Write-Host "Modulos SQLPS y SQLASCMDLets cargados correctamente" -fore Green
}
else
{
Write-Host "Fallo al cargar los modulos requeridos" -fore red
}
#Leer variables
$ASInstanceName = Read-Host "Introduzca la instancia Analysis Services a configurar"
$ASDatabaseName = Read-Host "Escriba el nombre de la base de datos (en blanco para aplicar a todas)"
$ASMemberName = Read-Host "Introduzca la cuenta que desea configurar"
$ASMemberName = CheckUser $ASMemberName
#Comprueba que el usuario existe
#El nombre del rol es requerido
$ASRolName = Read-Host "Escriba el nombre del Rol en el que va a agregar la cuenta [$ASMemberName] "
if ($ASRolName -eq $null)
{
Write-Host "Reinicie el script e introduzca un Rol para poder validarlo y configurar el usuario $ASMemberName" -fore red
exit
}
#Intenta obtener el nombre de instancia por el nombre introducido. Si solo existe una instancia da la opcion de continuar
if ($ASInstanceName -eq "")
{
Write-Host "No ha introducido ningun nombre de instancia, se tomara la instancia por
defecto de la maquina local"
write-host "Desea continuar? " -NoNewLine
write-host "(s) Si " -NoNewline -ForegroundColor Green
write-host " | " -NoNewLine
write-host " (n) No" -NoNewline -ForegroundColor Yellow
write-host " : " -NoNewline
$Continuar = Read-Host
If ($Continuar -ine "s") {Throw "Se requiere una instancia para continuar el proceso"}
else
{
$ASObj = dir sqlserver:sqlas | Where-Object { $_.IsHttpHost -eq $false }
$ASName = $ASObj.name
$ASInstanceName= $ASObj.name +"MSSQLSERVER"
}
}
else
{
if ($ASInstanceName -like "**")
{
$string = $ASInstanceName.Split("")
$ASName = $string[0]
#$ASInstanceName = $string[1]
}
else
{
$ASName = $SQLInstance
$ASInstanceName = $SQLInstance + "MSSQLSERVER"
}
$ASObj = dir SQLSERVER:SQLAS | Where-Object {$_.name -eq $ASName}
}
if ($ASObj -eq $null)
{break}
$ASInstance = $ASObj.AnalysisServers | Where-Object {$_.name -eq $ASInstanceName}
#Obtiene el objeto instancia sobre el que vamos a trabajar
if ($ASInstance -eq $null)
{
Write-Host "No se ha detectado la instancia por defecto en esta maquina" $ASInstanceName
if (($ASObj.AnalysisServers).count -eq 1 )
{
write-host "Se procedera con la instancia " $ASObj.AnalysisServers
write-host "Desea continuar? " -NoNewLine
write-host "(s) Si " -NoNewline -ForegroundColor Green
write-host " | " -NoNewLine
write-host " (n) No" -NoNewline -ForegroundColor Yellow
write-host " : " -NoNewline
$Continuar = Read-Host
If ($Continuar -ine "s") {Throw "Se requiere una instancia para continuar el proceso"}
$ASInstance = $Asobj.AnalysisServers[0]
}
else
{
write-host "Existen" ($ASObj.AnalysisServers).count "instancias. Por favor, reinicie
el script e introduzca alguna de las siguientes: " $ASObj.AnalysisServers
}
}
#Comprueba que la base de datos existe, si no se ha introducido ninguna el filtro seran todas (*)
if ($ASDatabaseName -ne "")
{
if ( ($ASInstance.Databases | Where-Object {$_.name -ieq $ASDatabaseName}).count -eq 0)
{
write-host "No se ha podido encontrar la base de datos " -ForegroundColor Red -NoNewline
write-host $ASDatabaseName -ForegroundColor White
if ($ASInstance.Databases.Count -eq 0)
{
write-host "No existen bases de datos en la instancia " $ASInstance.Name
}
else
{
Write-host "Reinicie el script e introduzca alguna de las siguientes: " -fore red| Out-host
forEach ($db in $ASInstance.Databases)
{
write-host " ? $db" -fore Red
}
}
}
}
else
{
$ASDatabaseName="*"
}
Write-host "***************************************************************************" -fore Yellow
Write-host ""
#Recorre todas las bases de datos utilizando el filtro generado en el paso anterior
ForEach ($DB in $ASInstance.Databases | Where-Object {$_.name -like $ASDatabaseName})
{
write-host "Accediendo a la base de datos $DB"
if ($DB.Roles.Count -ne 0)
{
#Agrega el usuario al rol que ha introducido
ForEach ($rol in $DB.Roles)
{
if ($rol.name -ine $ASRolName)
{
write-host " * rol [$rol] descartado" -fore gray
}
else
{
if (($rol.members | Where-Object {$_.Name -ilike $ASMemberName}).count -eq 0)
{
$rol | Add-RoleMember -MemberName $ASMemberName
write-host " * Usuario [$ASMemberName] agregado al rol [$rol] " -fore Green
}
else
{
write-host " * Usuario [$ASMemberName] ya existe en el rol [$rol] " -fore DarkYellow
}
}
}
}
else
{
Write-Host " * No existen roles en la base de datos $DB" -fore DarkYellow
}
}
Remove-Variable ASObj
Remove-Variable ASName
Remove-variable ASInstance
Remove-Variable ASInstanceName
Remove-Variable ASDatabaseName
Remove-Variable ASMemberName
#...
Write-hot ""
Write-host "Se ha completado el proceso con exito" -fore green
}
catch
{
Write-Host "Error: $_" -fore Red
Exit
}
Referencias
