“How To Became an Exceptional DBA” usando Powershell Parte I


 

articlepresentation_ST

Pessoal, Esta versão esta diferente da que saiu agora no simple-talk. A do simple-talk possui scripts mais atuais , usando advanced functions e powershell V2.0.

Exceptional PowerShell DBA Pt1 – Orphaned Users

 

"How to Became an Exceptional DBA"  é um daqueles handbooks que estão em minha coleção privada, ao lado no  “Inside SQL Server 2000” da Kalen Dalaney. São exemplos de livros que mudam seu jeito de pensar, trabalhar e entender as coisas.

Para ser sincero, a primeira vez  que li pensei : “A maioria é utopia”. Mas utópico era minha organização.

Eu não conseguia ver fora daquele “mundinho” que eu tinha criado com minhas ações profissionais que eu pensava não precisar de mais . Estava bom daquele jeito.

Hoje eu vejo que cada palavra que Brad McGehee escreveu neste sensacional handbook é perfeitamente aplicável a qualquer organização que você trabalhe. Seja pequena ou grande o que diferenciará , depois de ler, é seu jeito de pensar : Pequeno ou grande.

O ensinamentos deste handbook me influenciaram tanto que quando comecei a aprender o pouco que eu sei de Powershell eu pensei :

“Vou passar todos estes checklists que são sugeridos para Powershell”

E hoje meus amigos, eu compartilho com vocês alguns destes scripts que uso diariamente  baseado neste handbook .

Para não ser entediante a leitura ele foi dividido, e esta é a primeira parte.

How to Became an Exceptional DBA using Powershell Parte I
Check/Fix Usuários órfãos

 

O cenário

John IDontKnow, Robert IDontknowEither e você são DBAs de um grande datacenter em 3 turnos diários. O seu é das 7:00 AM as 16:00 PM. Todos os 3 tem autonomia suficiente para tomar decisões e medidas necessárias para o funcionamento do parque de servidores SQL Server do principal cliente da companhia, que compreende uma complexa estrutura OLTP e OLAP.

Seu gerente, um antigo DBA da companhia (sim, ele é técnico e dos bons) em uma reunião pediu para você procurar por usuários órfãos no ambiente  para implementarem uma politica de segurança adequada.

Você responde :
“Ok..Farei hoje isso “ 
Mas não seria melhor responder :
“Se você quiser a relação agora, eu a tenho. Implementamos um processo que roda toda semana e procura por usuários órfãos , logando eles numa tabela”

Você é um “Exceptional DBA” e usa Powershell !!!

Check/Fix Usuários órfãos

Neste script, trabalharemos com tratamento de erros, no caso de algum servidor estar offline.
Infelizmente este script está na versão 1.0  e o tratamento de erros nesta versão não é tão avançado quanto a 2.0.
Os próximos artigos da série provavelmente serão na 2.0

Nós trabalharemos com 2 funções. Uma para gerar o arquivo de log do tratamento de erros e outra para verificar se o login existe ou tem que ser criado.

A primeira coisa a ser fazer é criar 3 folders que terão o TXT com a lista de servidores, o arquivo de log e os scripts ps1 respectivamente.

Neste caso  :
Lista de servidores –>  “C:\PS\Servers\Servers.txt”,
Log estará            –>  “C:\PS\Logs\OrphanedUsers”
Scripts ps1           –>  “C:\PS\PSScripts”
 
O Arquivo TXT que terá a lista de servidores , deverá conter TODOS os servidores a serem monitorados e caso haja mais de uma instância , também deverá ser colocada :

 

clip_image001

Se você não tem o SQL Server  2008 instalado mas quer usar o provider, está é uma boa referência :

Installing SQL Server 2008 PowerShell snap-ins for SQL Server 2005 – Tim Benninghoff

Profile

“Quando você adicionar aliases, funções e variáveis, você está realmente adicionando-lhes apenas para a sessão atual do Windows PowerShell. Se você sair da sessão ou encerrar o Windows PowerShell, as alterações são perdidas.

Para manter estas mudanças, você pode criar um perfil do Windows PowerShell e adicionar os aliases, funções e variáveis para os perfis. O perfil é carregado toda vez que o Windows PowerShell é iniciado.

Para carregar um perfil, o Windows PowerShell política de execução deve permitir que você carregue os arquivos de configuração. Se isso não acontecer, a tentativa de carregar o perfil do Windows PowerShell falhar e exibe uma mensagem de erro.”

Windows Powershell Profiles

Ou seja, como veremos a execução do job tipo powershell e cmdexec, precisamos adicionar os providers do
SQL SERVER 2008 no powershell.exe. Lembre-se o host do SQL SERVER 2008 já vem, mas o shell normal não.
Olhe no path :

%windir%\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1

Abra este arquivo, se ele não existe crie com o nome acima.

if (!(Get-PSSnapin -registered | ?{$_.name -match ‘SqlServerProviderSnapin100’}))
{
       add-pssnapin SqlServerProviderSnapin100
       add-pssnapin SqlServerCmdletSnapin100
}
  

Desta maneira todos os shells e usuários estarão habilitados a usar o SQL SERVER provider.

Com isto pronto, vamos a função que gerará o log se tivermos problema e procurará pelos logins.

##########################################################
#Save  log in file
##########################################################
#File Name                        = $NamePS1
#Server name                      = $Server
#Error Message                    = $Erro
#Path to generate file     = $PathFileError
Function Save-Log ([String] $NamePS1,
                  [String] $Server,
                  [String] $Erro,
                  [String] $PathFileError,
                  [String] $TodayDate
                 )
{
    #test if path wich will contains the error file exists. if not create
  if (!(Test-Path -path $PathFileError))
  {
    New-Item $PathFileError
  }
    #Put the name of error file like  $Nameps1.log
  #$NameFileFull = $PathFileError + "\" + $NamePS1 + (get-date -format "yyyyMMddhhmmss") + ".log"
  $NameFileFull = $PathFileError + "\" + $NamePS1 + $TodayDate + ".log"
   "Server : " + $Server, "Date : " + $TodayDate ,"Error Message: " + $Erro | Out-file  $NameFileFull -append
}
##########################################################
#verify login exists
##########################################################
Function Get-Login (      [object] $Server,
                          [String] $LoginName
                    )
{  
$Collect = $Server.logins | where-object {$_.isdisabled -eq $False -and $_.IsSystemObject -eq $False -and $_.IsLocked -eq $False -and $_.name -eq $LoginName } 
if ($Collect -eq $Null)
       {return  $False }
else
       {return $True}
}     

 

O próximo passo é salvar este script no mesmo path para todos .ps1 . C:\PS\PSScripts\Functions.ps1 

Baixar Functions.ps1

Pronto podemos criar a tabela no repositório.

create table tblOrphanedUsers ([Date] smalldatetime default getdate(),
                              ServerName varchar(50),
                              [Database] varchar(50),
                              Users XML)
                               

 

E AGORA O SCRIPT PARA CHECAGEM :

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
# load fucntions file
. c:\ps\psscripts\Functions.ps1
$TodayDate = get-date -format "yyyy-MM-dd hh:mm:ss"
$TodayDateErr = get-date -format "yyyyMMddhhmmss"
##########################################################
#Server and Database Repository
##########################################################
$ServerRepository  = "SERVER1"
$DBRepository = "DBA"
$Path = "C:\PS\Logs\OrphanedUsers"
$Error.Clear()
foreach ($svr in get-content "C:\PS\Servers\servers.txt" )
{
             $MyError = 0
              trap  [Exception] {
                    $Err = $_.Exception.Message
                    Save-Log "OrphanedUSers" "$svr" "" "$Err" "$Path" "$TodayDateErr"
                    $MyError = 1
                    $Error.Clear()
                    continue;
                }  
        $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr" -ErrorAction  stop
       # if service off, then not test
       if ($MyError -eq 0)
       {
             $Server.Databases  | where-object {$_.IsSystemObject -eq $FALSE -and$_.IsAccessible -eq $TRUE } | foreach {
                    $Database = $_.name
                    foreach  ($user in $Server.Databases[$_.name].users ) {
              if (!$user.IsSystemObject -and $user.Login -eq "" )
                    {
                           $Insert =  !(Get-Login $Server $user.name)
                           $ObjectXML = New-Object PSObject
                           $ObjectXML | add-member Noteproperty UserName $user.name
                           $ObjectXML | add-member Noteproperty CreateLogin $Insert
                           $ObjectXML | export-clixml C:\ps\logs\Users.xml
                           $XML= Get-Content "C:\ps\logs\Users.xml"
                           $XML = $XML -replace "’", "”"
                           $SQL = "     Insert into tblrphanedUsers ([Date],ServerName,[Database],Users)       values (‘$TodayDate’,’$svr’,’$Database’,’$XML’)"
                           Invoke-Sqlcmd -ServerInstance $ServerRepository -Database $DbRepository -Query $Sql -ErrorAction SilentlyContinue
                           if (!$?)
                           {
                                 Save-Log "OrphanedUSers" "$svr" "$Database" "$Error" "$Path" "$TodayDateErr"
                                  $Error.Clear()
                           }
                             del C:\ps\logs\Users.xml -ErrorAction SilentlyContinue
                        }           
                    }     
             }     
       }
}

 

O próximo passo é gravar este script em C:\PS\PSScripts\OrphanedUsers.ps1

Baixar OrphanedUsers.ps1

A função Save-Log gera um arquivo de LOG com os erros encontrados e grava na pasta C:\PS\Logs\OrphanedUsers  com o mesmo nome do .ps1 mais data e hora , que neste caso é OrphanedUsers.ps1. A figura abaixo mostra isso :

(CLIQUE NELAS PARA AUMENTAR)

clip_image002

clip_image002[5]

Agora se quisermos ver os dados que foram logados na tabela :

clip_image002[7]

Mas se percebermos no código em powershell eu coloquei esta linha

Insert =  !(Get-Login $Server $user.name)

Isso se deve ao fato que podemos ter dois tipos de usuários órfãos :

  • Login Não Existe
  • Login existe, mas não está mapeado com o usuário

E clicando na coluna Users abrirá o XML e no nó CreateLogin eu tenho esta informação. Se preciso criar ou mapear.

clip_image002[9]

Agora para visualizarmos os dados com a ultima coleta, usamos :

SELECT      [Date],
            ServerName,
            [Database],
users.value(
declare namespace s="http://schemas.microsoft.com/powershell/2004/04";
(/s:Objs/s:Obj/s:MS/s:S/text())[1]’, ‘varchar(255)’) UserName,
users.value(
declare namespace s="http://schemas.microsoft.com/powershell/2004/04";
(/s:Objs/s:Obj/s:MS/s:B/text())[1]’, ‘varchar(255)’) CreateLogin
FROM tblOrphanedUsers
where [Date] = (  select      MAX([date])
                             from  tblOrphanedUsers

                )

 

clip_image002[11]

Mapeando User com Login

Como já temos a informação dos logins a serem criados antes de mapear pela coluna CreateLogin , vamos gerar os comandos para mapear os que não precisam ser criados ou seja, já existem. (createlogin = false)

Podemos fazer em TSQL (Visualizar em Modo Texto)

SELECT ‘Use ‘ + [Database] + CHAR(13) + CHAR(10) + ‘ go ‘ + char(13) + CHAR(10)
+ ‘ exec sp_change_users_login ”Update_one”,”’ +
users.value(
declare namespace s="http://schemas.microsoft.com/powershell/2004/04";
(/s:Objs/s:Obj/s:MS/s:S/text())[1]’, ‘varchar(255)’) + ”’,”’ +
users.value(
declare namespace s="http://schemas.microsoft.com/powershell/2004/04";
(/s:Objs/s:Obj/s:MS/s:S/text())[1]’, ‘varchar(255)’) + ”” + CHAR(13) + CHAR(10) + ‘ go ‘ + CHAR(13) + CHAR(10)
FROM tblOrphanedUsers
where [Date] = (    select MAX([date])
                    from   tblOrphanedUsers
                    )     
and    users.value(
declare namespace s="http://schemas.microsoft.com/powershell/2004/04";
(/s:Objs/s:Obj/s:MS/s:B/text())[1]’, ‘varchar(255)’) = ‘False’

 

O resultado será :

Use DBA
go
exec sp_change_users_login ‘Update_one’,’TesteUsuarioOrfao’,’TesteUsuarioOrfao’
go
Use Teste
go
exec sp_change_users_login ‘Update_one’,’TesteUsuarioOrfao’,’TesteUsuarioOrfao’
go

 

Podemos usar a opção AUTO_FIX na sp_change_users_login,mas eu acredito ser melhor por questões de segurança analisar primeiro antes de criar o Login.

Mais sobre sp_change_users_login ? (eu coloquei por questões de compatibilidade , procure por alter login)

 

OU PODEMOS REPARAR USANDO POWERSHELL

(não se esqueça, você é um “Exceptional DBA” e usa powershell)

Poderíamos alterar o script powershell para que depois de inserido na tabela, já mapeasse o login.
Mas eu particularmente prefiro ver antes. Então após analisar, vamos na tabela que está logado os users : tblOrphanedUsers

$SqlConn1 = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd1 = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter1 = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet1 = New-Object System.Data.DataSet
$sql = "SELECT    ServerName,
                [Database],
users.value(‘
declare namespace s=""
http://schemas.microsoft.com/powershell/2004/04"";
(/s:Objs/s:Obj/s:MS/s:S/text())[1]’, ‘varchar(255)’) UserName
FROM tblOrphanedUsers
where [Date] = (    select    MAX([date])
                    from    tblOrphanedUsers
                )   
and     users.value(‘
declare namespace s=""
http://schemas.microsoft.com/powershell/2004/04"";
(/s:Objs/s:Obj/s:MS/s:B/text())[1]’, ‘varchar(255)’) = ‘False’"

$SqlConn1.ConnectionString = "Server=<ServerRepository>;Database= DatabaseRepository>;Integrated Security=True"
$SqlCmd1.CommandText = $sql
$SqlCmd1.Connection = $SqlConn1
$SqlAdapter1.SelectCommand = $SqlCmd1
$SqlAdapter1.Fill($DataSet1)

foreach ($result in $DataSet1.Tables[0].rows) {
    $SqlConn1.ConnectionString = "Server=" + $result[0] + ";Database=" + $result[1] + ";Integrated Security=True"
    $sqlconn1.Open()
    $SqlCmd1.Connection = $SqlConn1
    $SqlCmd1.CommandText = "sp_change_users_login ‘update_one’,’" + $result[2]+"’,’" +  $result[2] + "’"
    $SqlCmd1.ExecuteNonQuery()
    $SqlConn1.Close()   
}

 

Baixar este script  FixUserLogin.ps1

Na linha :
$SqlConn1.ConnectionString = "Server=<ServerRepository>;Database= <DatabaseRepository>;Integrated Security=True"

 

Alterar para para o nome de seu servidor e database.
Este script eu não trato erros, pois rodo manualmente sem jobs .

 

Schedulando a Checagem

Se você estiver usando o SQL Server 2008 somente basta criar um job com step tipo powershell e executar o script. A figura abaixo demonstra isso :

clip_image002[2]

Command :

c:\ps\psscripts\OrphanedUsers.ps1

 

Se estiver usando versões anteriores (inclusive a 2000 depois de ter adicionado os providers e baixado o powershell), basta criar um job com  step cmdexec conforme mostra figura abaixo :

clip_image002[4]

Command :

Powershell.exe ‘c:\ps\psscripts\OrphanedUsers.ps1’

 

Depois  é só schedular conforme sua necessidade !!!!!!!

Bom pessoal, vimos nesta primeira parte como checar e reparar usuários órfãos.

Eu particularmente quando preciso de um processo que seja repetido várias vezes uniformemente e gerenciando múltiplos servidores (isto serve para 1 ou 1000 servidores) não consigo mais ver outra maneira sem ser com a consistência e produtividade do Powershell .

Nos próximos artigos veremos mais checagens, fazendo de você um

“Exceptional DBA” usando Powershell !!!!!

Hahaaaaa  acharam que eu ia esquecer néééééé !!!!

POWERSHELL ROCKS !!!!

Finalizei a versão em português ouvindo Ramones……E este som merece estar inteiro :

No one ever thought this one would survive
Helpless child, gonna walk a drum beat behind
Lock you in a dream, never let you go
Never let you laugh or smile, not you.

Well, I just want to walk right out of this world,
‘Cause everybody has a poison heart
I just want to walk right out of this world,
‘Cause everybody has a poison heart.

Making friends with a homeless torn up man
He just kind of smiles, it really shakes me up.
There’s danger on every corner but I’m okay
Walking down the street trying to forget yesterday.

Well, I just want to walk right out of this world,
‘Cause everybody has a poison heart.
I just want to walk right out of this world,
‘Cause everybody has a poison heart,
a poison heart, a poison heart, a poison heart … yeah!

You know that life really takes its toll
And a poet’s gut reaction is to search his very soul
So much damn confusion before my eyes,
But nothing seems to phase me and this one still survives.

I just want to walk right out of this world,
‘Cause everybody has a poison heart.
I just want to walk right out of this world,
‘Cause everybody has a poison heart,
Well, I just want to walk right out of this world,
‘Cause everybody has a poison heart.
a poison heart, a poison heart, a poison heart.
a poison heart, a poison heart, a poison heart, a poison heart.

Poison Heart

Ramones

 

Tks a meu amigo Felipe Ferreira que me ajudou no Xquery !!!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. Bookmark the permalink.

4 Responses to “How To Became an Exceptional DBA” usando Powershell Parte I

  1. Felipe says:

    mto bom artigo Laerte! vou testar esses scripts no meu ambiente de testes pra estudar mais ele! :De precisando de ajuda com xquery é só chamar o/abs

  2. Laerte says:

    Valeu velhinho…COM CERTEZA VOU PRECISAR !!!!..se não fosse tu o treco ia demorar pra sair hahaahhaahah !!!!!!

  3. Vladimir Michel says:

    Grande Laerte!! Show de bola como sempre! Agora tem de fazer em PS 2.0!Em breve vou testar essa rotina para colocar em produção 🙂

  4. Laerte says:

    Grande Vladimir hehheh!!!! Obrigado meu amigo !!! Bom tu ter aparecido por aqui..!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s