Click here to monitor SSC
  • Av rating:
  • Total votes: 47
  • Total comments: 30
Laerte Junior

Exceptional PowerShell DBA Pt1 - Orphaned Users

26 November 2009

Inspired by Brad McGehee's 'How to Become an Exceptional DBA', Laerte decided it was high time he took a proactive approach to his work. Using PowerShell, he came up with a solution that makes finding and fixing orphaned users as easy as cake, and now he's sharing it with us.

Brad McGehee's "How to Become an Exceptional DBA" is one of the more well-thumbed books in my library, right next to Kalen Delaney's "Inside SQL Server 2000". Brad's book is an excellent example of recommended reading for changing the way you think and work. I admit that, the first time I read it, I thought many of Brad's suggestions were, quite frankly, utopian. But it turns out that the 'utopia' is actually a realizable goal.

Like many of us, I have been guilty of not seeing beyond the little world of my immediate professional actions, and of being confident that nothing beyond those actions was needed for me to do my job well. With hindsight, I will say that every word Brad writes his sensational handbook is perfectly applicable to any organization, be it small or large. What makes a difference is the scale of your thinking: Big or Small.

Brad's lessons influenced me so much that, when I learned what little I know of PowerShell, I decided to turn his DBA checklists into PowerShell scripts, and I'd like to share some of these scripts (which I use daily) with you. To make sure this is not tiresome reading, I've divided my scripts into 3 parts, and the first is: Check/Fix the Orphan Users.

I actually decided to start with this example because it was an almost daily procedure for me (due to the circumstances at the time), and it took a considerable amount of time. As we are lovers of PowerShell, I also want to share with you how this fantastic shell solved my problem with a uniform, repeatable and consistent solution. We'll cover more usual situations in the future articles in this little series

A Little Background

John IDontKnow, Robert IDontknowEither and You work as DBAs for a major data center in 3 daily shifts, of which yours is from 7:00 AM to 16:00 PM. All three of you have sufficient autonomy to do what you need, and you are responsible for the center's top client, who has a complex evolving OLTP and OLAP infrastructure, and a ton of SQL Servers.

You have a large security issue that affects your work: For contractual reasons, some of your client’s employees have access to production servers, and for their own internal reasons they drop logins almost daily. And just for fun, some logins are recreated again at later dates, but not with the same SID, making them Orphan Users. As a result, one of your main routines in the morning (and it takes a reasonable amount of time) is to check for these orphaned users.

Your manager, a senior DBA in the company (*gasp* a tech-savvy manager) asks you to search for all the orphaned users in the environment in order to implement an appropriate security policy and send it to the client.
    "At last!" you happily respond, "OK, I'll do it today".
Surely it would have been better to have answered:
    "If you want to see that data now, I have a process that runs every night looking for orphaned users, logging
    the data in a table"

After all, you are an Exceptional DBA and you use PowerShell.

Check/Fix the Orphan Users

In this script, we will work with PowerShell 2.0 and error handling (in case the SQL Servers are offline or some other problem occurs), and we will also be using a function module (.psm1) to contain the functions that we'll use in our scripts and make them easily available; It contains:

Save-MSSQLMsg :
This function generates a log file for scripts (Success, error , or simply an information message)

Map-MSSQLUser
This function maps the user with their Login - It only maps Users and Logins with the same name.

Drop-MSSQLUser
This function drops any users who don't have a Login (it checks for Logins and Users with the same name, again)

Get-MSSQLUser
This function Returns all an object with information about users :

LineNumber, Date, ServerName, DatabaseName, UserName, CreateDate,
DateLastModified, AsymMetricKey, DefaultSchema, HasDBAccess,ID, LoginType, Login,
Orphan, LoginExists

The Orphan and LoginExists properties let you know whether a User is orphaned (i.e. does not have a Login) and whether there is Login exists with the same name as, but is not mapped to, this user.

The first thing to do is create the two folders for the server list file and the ps1 script files (and function.psm1 module) respectively. In this case:

  • The servers list file is stored in C:\PS\servers\
  • The log file is created in C:\TEMP, which is the default  path from the Save-MSSQLMsg function. As this path is passed as a parameter, you can very simply  change this to put in a new path where the function is called
  • And ps1 scripts (and function.psm1 module) are in C:\PS\PSScripts

The flat file containing the list of servers will have to contain all SQL Servers in your environment, and if you have more than one instance that you want to monitor, each instance must also be listed. It should looks like this:

Server1
Server2
Server3\Instance1
Server3\Instance2

If you do not have SQL Server 2008 but still want to use the 2008 PowerShell Provider, then Tim Benninghoff has an excellent guide on how to make that possible. However, you will have take 'Profiles' into account if that is the case.

PowerShell Profiles

When you add aliases, functions, and variables, you are actually adding them only to the current Windows PowerShell session. If you exit the session or close Windows PowerShell, the changes are lost.

To retain these changes, you can create a Windows PowerShell profile and add the aliases, functions, and variables to the profiles. The profile is loaded every time that Windows PowerShell starts.”

Windows PowerShell Profiles

So how are we going to use a CmdExec job with the invoke-cmd cmdlet when the SQL Server 2008 Provider isn't installed? Simply adjust all user profiles to load the appropriate Snapin and provider from SQL Server 2008, and avoid the problem.  We also load our function module (.psm1) into the profile So that all the required functions will be available when we start the Shell.To do that, open this file (or create it if it doesn't already exist):
%windir%\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1
...and type:

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

Import-Module -Name'C:\PS\PSScripts\Functions.psm1' -WarningAction SilentlyContinue  -ErrorAction Stop

If you want to see the snap-ins registered, start a new PowerShell session, type:

Get-PSSnapin – Registered

… And the SQL Server 2008 should be there, as shown below :

Registered Snapins

We can see our functions module in the same way; just type:

Get-Module

And you should see this:

Registered Modules

Now all shells and users will be able to use the SQL Server snap-in and our functions.

The Scripts

With that done, lets actually create that function script module (functions.psm1) that will do the work of generating logs and mapping or dropping logins.

Function Save-MSSQLMsg () 
<#
----------------------------------------------------------
Save  log in file
----------------------------------------------------------
Requires POWERSHELL 2.0

File Name               = $NamePS1
Server name             = $Server 
DatabaseName            = $databasename
Message To Log          = $Message
Path to generate file   = $PathFileLog Default = C:\temp
Date to Log             = $TodayDate
#>

{

[CmdletBinding()]

Param (
[Parameter(position=1,Mandatory = $true )][String] $NamePS1,
[Parameter(position=2,Mandatory = $true )][String] $Server,
[Parameter(position=3,Mandatory = $false )][String] $DatabaseName = "",
[Parameter(position=4,Mandatory = $false )][String] $Message = "" ,
[Parameter(position=5,Mandatory = $false )][String] $PathFileLog = "C:\temp",
[Parameter(position=6,Mandatory = $false )][String] $TodayDate = (Get-Date -Format 
                                                                  "yyyyMMddhhmmss")
)
process 
{

#test if path wich will contains the error file exists. if not create 

if (!(Test-Path -path $PathFileLog))
{
try {
    New-Item $PathFileLog -itemtype directory -ErrorAction  Stop   | Out-Null
}
catch {
    Write-Host "Can not create log file path"
    break;
}
} 


$NameFileFull = $PathFileLog + "\" + $NamePS1 + $TodayDate + ".log" 

$TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2) + "-" 
         + $TodayDate.Substring(6,2) 

"Server : " + $Server + " Database : " + $DatabaseName + " Date : "  + $TDate + 
          " Message: "  + $Message | Out-file  $NameFileFull -append 
} 
}


Function Map-MSSQLUser()
{
<#
----------------------------------------------------------
Map USer and Logins
----------------------------------------------------------
Requires POWERSHELL 2.0
Server Name         = $Server
Database Name       = $Database
USer Name           = $USer

#>

[CmdletBinding()]

Param ( 
    [Parameter(Mandatory = $true )][string] $Server,
    [Parameter(Mandatory = $true )][String] $Database,
    [Parameter(Mandatory = $true )][String] $USer
    )
    
process
{

$SqlConn = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

try{
    $SqlConn.ConnectionString = "Server=" + $Server+ ";Database=" + $Database + 
                                ";Integrated Security=True"
    $sqlconn.Open()
    $SqlCmd.Connection = $SqlConn
    $SqlCmd.CommandText = "ALTER USER " + $USer + " WITH LOGIN = " + $USer
    $SqlCmd.ExecuteNonQuery()
} catch {
            $msg = $error[0]
            Write-Warning $msg
            Save-MSSQLMsg "Map-MSSQLUser" "$svr" "$Database" "$msg" 
}           
finally {
    $SqlConn.Close()
}
}

}


Function Drop-MSSQLUser ()
{
<#
----------------------------------------------------------
Drop USer without Login
----------------------------------------------------------
Requires POWERSHELL 2.0
Server Name         = $Server
Database Name       = $Database
USer Name           = $USer

#>

[CmdletBinding()]

Param ( 
    [Parameter(Mandatory = $true )][string] $Server,
    [Parameter(Mandatory = $true )][String] $Database,
    [Parameter(Mandatory = $true )][String] $USer
    )
    
process
{

$SqlConn = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

try{
    $SqlConn.ConnectionString = "Server=" + $Server+ ";Database=" + $Database + 
                                ";Integrated Security=True"
    $sqlconn.Open()
    $SqlCmd.Connection = $SqlConn
    $SqlCmd.CommandText = "Drop  USER " + $USer 
    $SqlCmd.ExecuteNonQuery()
} catch {
            $msg = $error[0]
            Write-Warning $msg
            Save-MSSQLMsg "Drop-MSSQLUser" "$svr" "$Database" "$msg" 
}           
finally {
    $SqlConn.Close()
}
}

}


Function Get-MSSQLUser() 

{
<#
TXTServersList = File List (.txt) with  the servers. Default is the current server

#>


[CmdletBinding()]

PARAM(

    [Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true,
               HelpMessage="File Servers List")]
    [Alias("FullNameTXT")]
    [String] $TXTServersList = $env:COMPUTERNAME

)   


begin
{
    
$verbosePreference="continue" 
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
}
process 
{

try 
{

    if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT")
    {
        try
        {
            $ServersList = get-content $TXTServersList  
        } catch {
                    $msg = $error[0]
                    Write-Warning $msg
                    break;
        }
    }   
    else
    {
        $ServersList = $TXTServersList
    }   
    
    
    $LineNumber = 1
    $FinalResult = @()

    foreach ($svr in  $ServersList )
    {
        try 
        {
            $Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
            $Server.Databases  | where-object {$_.IsSystemObject -eq $FALSE -and 
                                               $_.IsAccessible -eq $TRUE } | foreach {
        
                $Database = $_.name
                foreach  ($user in $Server.Databases[$_.name].users ) {
                
                    $Object = New-Object PSObject
            
                    [datetime] $CreateDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] 
                                             $user.CreateDate  
                    if ($user.DateLastModified -ne $null)
                    {
                        [datetime] $DateLastModified = "{0:yyyy/MM/dd hh:mm:ss}" -f 
                                                    [datetime] $user.DateLastModified 
                    }                               
                
               $Object | add-member Noteproperty LineNumber       $LineNumber     
               $Object | add-member Noteproperty Date             $TodayDate  
               $Object | add-member Noteproperty ServerName       $svr
               $Object | add-member Noteproperty DatabaseName     $Database
               $Object | add-member Noteproperty UserName         $user.name
               $Object | add-member Noteproperty CreateDate       $CreateDate
               $Object | add-member Noteproperty DateLastModified $DateLastModified
               $Object | add-member Noteproperty AsymMetricKey    $user.AsymMetricKey
               $Object | add-member Noteproperty DefaultSchema    $user.DefaultSchema
               $Object | add-member Noteproperty HasDBAccess      $user.HasDBAccess
               $Object | add-member Noteproperty ID               $user.ID
               $Object | add-member Noteproperty LoginType        $user.LoginType
               $Object | add-member Noteproperty Login            $user.Login
               $Object | add-member Noteproperty Orphan           ($user.Login -eq "")
                    
                    $ExistLogin = $Server.logins | where-object {$_.isdisabled -eq 
                                  $False -and $_.IsSystemObject -eq $False -and 
                                  $_.IsLocked -eq $False -and $_.name -eq $user.name }  
                    
                    if (!$ExistLogin)
            
                    {   $Object | add-member Noteproperty LoginExists       $False  }
                    else
                    {   $Object | add-member Noteproperty LoginExists       $True   }   
                    
                    
                    $FinalResult += $Object
                    $LineNumber ++ 
                } 
                
            }   
            
            
        } catch {
                $msg = $error[0]
                Write-Warning $msg
                Save-MSSQLMsg "Get-MSSQLUser" "$svr" "$Database" "$msg" 
        } Finally { 
                continue    
        }
    }
    Write-Output $FinalResult   
    
}catch {
        $msg = $error[0]
        Write-Warning $msg
        Save-MSSQLMsg "Get-MSSQLUser" "" "$Database" "$msg" 
}
}   

}

This is saved in the Windows Powershell Script Module format  file (.psm1)  under the file path for all of our scripts (C:\PS\PSScripts\Functions.psm1) . Some sample commands you could pass to this module include:

#Return All Users (current server - we do not pass .txt) and all Properties
Get-MSSQLUser    

#Return All Users and all Properties by .txt
Get-MSSQLUser C:\PS\servers\servers.txt

Now it's time to look for those Orphaned Users.

How to Return Orphaned Users

The Orphan property tells us if a given user has a associated Login. If not, it's a orphaned user – it's that simple. We use it in conjunction with the hasdbaccess property too, to list only users with access to the database in question.

#Return All Orphaned Users By .txt
Get-MSSQLUser C:\PS\servers\servers.txt | Where-Object {$_.orphan -eq $true -and 
        $_.hasdbaccess -eq $true } 
        | format-table Date,ServerName,DatabaseName,UserName,Login,Orphan,LoginExists

#Return All Orphaned Users  current server
Get-MSSQLUser | Where-Object {$_.orphan -eq $true -and $_.hasdbaccess -eq $true } 
        | format-table Date,ServerName,DatabaseName,UserName,Login,Orphan,LoginExists

We can also list all users who are orphaned but share their name with a Server Login; we  just need to add the LoginExists property to the conditions.

#Return All Orphaned Users By .txt
Get-MSSQLUser C:\PS\servers\servers.txt | Where-Object {$_.orphan -eq $true -and 
         $_.hasdbaccess -eq $true -and $_.LoginExists -eq $true } 
         | format-table Date,ServerName,DatabaseName,UserName,Login,Orphan,LoginExists

Any users this returns just need to be mapped with their logins.

What about outputting this data to a SQL Server table?

First, create the necessary tblOrphanedUsers table in the data repository…

CREATE TABLE tblOrphanedUsers    ( Users XMLCol
    
)

And then run this:

(Get-MSSQLUser  | Where-Object {$_.orphan -eq $true -and $_.LoginExists -eq $true} 
| select Date,ServerName,DatabaseName,UserName,Login,Orphan,LoginExists 
| ConvertTo-Xml -NoTypeInformation).save("c:\temp\Users.xml") 
$XML = (Get-Content c:\temp\users.xml ) -replace "'", "''"
$SQL = "insert into tblOrphanedUsers (XMLCol) values ('$XML')" 
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql

You can choose the properties, path and name of your XML File, and obviously you server and database repositories ( -ServerInstance and –database). But you want to get this information  in a SQL table, yes? No problem, It's a simple Xquery, and you can see the results below:

SELECT    t2.Col1.value('(Property[@Name="Date"]/text())[1]', 'nvarchar(255)') [Date],
            
t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(255)')
            ServerName,
            
t2.Col1.value('(Property[@Name="DatabaseName"]/text())[1]',
            
'nvarchar(max)') DatabaseName,
            
t2.Col1.value('(Property[@Name="UserName"]/text())[1]', 'nvarchar(255)')
            
UserName,
            
t2.Col1.value('(Property[@Name="Login"]/text())[1]', 'nvarchar(255)')
            
[Login],
            
t2.Col1.value('(Property[@Name="Orphan"]/text())[1]', 'nvarchar(255)')
            
Orphan,
            
t2.Col1.value('(Property[@Name="LoginExists"]/text())[1]', 'nvarchar(255)'
            ) LoginExists
    
FROM dbo.tblOrphanedUsers
            
CROSS APPLY XMLCol.nodes('/Objects/Object') AS t2(Col1)
    
WHERE t2.Col1.value('(Property[@Name="ServerName"]/text())[1]', 'nvarchar(max)') =             'Jupiter'

SQL data output

How to Fix Orphaned Users with Powershell

In this case, we can fix these orphaned users in two ways. One is by mapping them to their Login (which has the same name) and another is by dropping the users who don't share their names with a Login. Let's take a look at these processes now.

Mapping Users with their Login

Nothing could be simpler. First, filter the objects by the orphan and LoginExists properties (the latter being ‘true'), and then call the Map-MSSQLUser function:

#Mapping USers with Logins
foreach ($user in Get-MSSQLUser c:\PS\Servers\servers.txt | Where-Object 
{$_.orphan -eq $true -and $_.LoginExists -eq $true} ) {
Map-MSSQLUser $user.ServerName $user.DatabaseName $user.UserName
}

Dropping users without a Login

This is just as simple to do as the mapping; we just change the LoginExists filter to ‘false', and the call the Drop-MSSQLUser function  from our PowerShell module:

#Dropping users without login
foreach ($change in Get-MSSQLUser    c:\PS\Servers\servers.txt | Where-Object 
{$_.orphan -eq $true -and $_.LoginExists -eq $false} ) {
Drop-MSSQLUser $change.ServerName $change.DatabaseName $change.UserName
}

Bear in mind that if we omit the server path in the Get-MSSQLUser function call, the current server is used.

As I said earlier, for the purposes of keeping track of what's going on, the Save-MSSQLMsg () function generates a log file of errors ,successes and even just status messages in the $PathLogFile folder (in this case we use default value c:\temp). To identify each log, it uses the name of the running script (passed as a parameter) plus the date and time, as the figure below shows:

OrphanUser Logs

This will make tracking down the details for any particular scheduled job incredibly easy, and as you can see, the log contents themselves are fairly well formatted:

log contents

Scheduling the Check.

When scheduling, we can simply take the examples of mapping and dropping users above, save them into .ps1 file and run it. Let´s say we save script to Map users (as seen above) into a file called MapOrphanedUsers.ps1 . If you are using SQL Server 2008, you would just need to then create a PowerShell Job and run the .Ps1 script by entering the storage path of your scripts into the ‘command' field:

c:\ps\psscripts\MapOrphanedUsers.ps1

PowerShell Job

Alternatively, you can run the script with a cmdexec-type job, ensuring the command field contains:

powershell -noprofile -Noninteractive -command c:\ps\psscripts\OrphanedUsers.ps1

After that, you just need to schedule the job, and you're finished! Clearly, this is a very particular case where others users can drop logins behind your back, but let's go to a more normal scenario:

Mapping Users after Restore

In this Datacenter, You have a Job that runs every Sunday and restores the production Databases to a development environment. So in this case, your production server X restores N databases to the development server D1, the production server Y restore N databases for development D2... Etc.

So , to make sure you can chose which file list the servers will be passing as parameters in your powershell script, you configure a flat ServersRestore.txt file especially for this scenario, and it looks like this :

Earth, DB1, DB2, DB3
Earth\Instance1. DB7, DB8
Sun, DB3, DB2, DB4

The list format is, fairly obviously, Server Instance followed by individual databases to be restored, all separated by commas. With that done, you configure one last job to run at 6:00 AM (after all the other restore jobs have finished) to fix the inevitable orphaned users in all the servers and databases listed in ServersRestore.txt. In this script, we load this flat file of servers and databases to check, and then the process proceeds in a very similar way to what I've outlined above.

The Orphan property tells us the relevant status of a user and LoginExists will let us decide which action needs to be taken; if LoginExists is false, we drop this user, and if it's True we map them.

foreach($ServerList in Get-Content c:\TEMP\ServersRestore.txt)
{
    [array]$ServerDBList = $ServerList.split(",")
    $TotalServerDBList = ($ServerDBList.count) -1
    
    $ServerName = $serverdblist[0]
    for ($Count = 1;$Count -le $TotalServerDBList;$Count++)
    {
        $DatabaseName =$serverdblist[$count]
        
        foreach ($Db in Get-MSSQLUser | Where-Object {$_.orphan -eq $true -and 
              $_.servername -eq $ServeraName -and $_.Databasename -eq $DatabaseName })    
        {
            if ($_.LoginExists -eq $false)
            {
                Drop-MSSQLUser $Db.ServerName $Db.DatabaseName $db.username
            }
            else
            {
                Map-MSSQLUser $Db.ServerName $Db.DatabaseName $db.username
            }
    
        }
    }
}

(This script as available for download at the top of this article.)

If you want to log all the completed transactions, you just need to add the Save-MSSQLMsg function after each action (drop or map), and then if some problem occurs when trying to Map or Drop, the error message is already logged and ready for inspection.

You can easily apply these functions to all databases in your environment, too. “Why would I want to do that?”, you ask. How about a migration of servers using backup and restore? Regardless of when and where you use these scripts, they can be scheduled in exactly the same way as I demonstrated earlier.

That’s everything for this first part of my script-sharing, and this is how I check and fix orphaned users. You can find a complete set of example scripts available for download at the top right of this article. Later on I will be showing you more daily checks, making you an Exceptional DBA, and using PowerShell.

Laerte Junior

Author profile:

Laerte Junior is a PowerShell MVP and, through his technology blog and simple-talk articles, an active member of the Microsoft community in Brasil. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and Powershell Programming with over 8 years of hands-on experience. He holds a degree in Computer Science, has been awarded a number of certifications (including MCDBA), and is an expert in SQL Server 2000 / SQL Server 2005 / SQL Server 2008 technologies. He also organizes, and is a speaker at microsoft community events, attracting hundreds of attendees. Laerte has also recently become a Friend of Redgate in Brasil, has taught classes at universities, and produced webcasts for the community.

You should follow him on Twitter as @LaerteSQLDBA

Search for other articles by Laerte Junior

Rate this article:   Avg rating: from a total of 47 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: This article
Posted by: Fabio Cotrim (not signed in)
Posted on: Friday, November 27, 2009 at 2:11 PM
Message: Laerte, thanks for this article. It's very good!!!

PERFECT!!!

Subject: nice
Posted by: Felipe Ferreira (not signed in)
Posted on: Friday, November 27, 2009 at 2:44 PM
Message: great article. congrats my friend.. keep doing this great work and helping the powesheel comunity :D

Subject: Maravilha
Posted by: Fábio Domingues (not signed in)
Posted on: Friday, November 27, 2009 at 3:00 PM
Message: Laerte,
Adorei o artigo, continue contribuindo a sua experiênciac com powersheel pois cada vez mais eu avanço a minha experiência com Sql Server.

Obrigado!

Subject: Wonderful
Posted by: Fábio Domingues (not signed in)
Posted on: Friday, November 27, 2009 at 3:02 PM
Message: Laerte,
I loved the article, continue contributing to your experiênciac with powersheel because more and more I advance my experience with SQL Server.
Thanks!

Subject: Very interesting
Posted by: Marcos Soeiro (not signed in)
Posted on: Friday, November 27, 2009 at 3:46 PM
Message: Nice post dude.

I like this alternative of fix orphaned users.

Continue with this great job, sharing your knowledge with community.

Thanks for all advices:)

Subject: Congratulations Laerte
Posted by: Demétrio Silva (not signed in)
Posted on: Friday, November 27, 2009 at 5:30 PM
Message: Excellent article. As all the que você faz. A good tip for those who work in day-to-day as DBA. Useful tasks.

Hugs

Subject: Congratulations Laerte
Posted by: Demetrio Silva (view profile)
Posted on: Friday, November 27, 2009 at 5:34 PM
Message: Excellent article. As all the que você faz. A good tip for those who work in day-to-day as DBA. Useful tasks.

Hugs

Subject: Great Article
Posted by: Paula Polsi (not signed in)
Posted on: Friday, November 27, 2009 at 6:25 PM
Message: Very interesting.Congrats.

Subject: Excellent Laerte.
Posted by: Math (not signed in)
Posted on: Saturday, November 28, 2009 at 7:25 AM
Message: I like your articles because they are experiences of your day to day. Even I began to study powershell. Keep up the good work.

Subject: Very, very good!!!
Posted by: Fábio - LIVETEC INFORMÁTICA (not signed in)
Posted on: Saturday, November 28, 2009 at 6:18 PM
Message: Very good!
I confess I got interested more by powershell after I started reading your articles. That's what we need professionals who have real knowledge and willing to exchange information.
Big hug.

Subject: Parabéns
Posted by: Carlos Ligeiro (not signed in)
Posted on: Saturday, November 28, 2009 at 7:13 PM
Message: Laerte meu caro muito bom seu artigo.
Você está de parabéns!!!

Subject: Great!!! Great!!! Great!!!
Posted by: Paulo R. Pereira (view profile)
Posted on: Saturday, November 28, 2009 at 7:17 PM
Message: Hi Laerte! Your articles are EXCELENT!
Your ideas are showing how PowerShell is REALLY useful!

Subject: Great!!! Great!!! Great!!!
Posted by: Paulo R. Pereira (view profile)
Posted on: Saturday, November 28, 2009 at 7:18 PM
Message: Hi Laerte! Your articles are EXCELENT!
Your ideas are showing how PowerShell is REALLY useful!

Subject: Àmazing article
Posted by: Eduardo Bergantini Pinto (not signed in)
Posted on: Saturday, November 28, 2009 at 8:54 PM
Message: Congratulations Laerte, for another amazing article. I'm go glad to read it and implement/ test.
Congratulations once again.

Subject: 10
Posted by: Alexandre (not signed in)
Posted on: Sunday, November 29, 2009 at 11:40 AM
Message: Mais uma vez na medida certa pro nosso dia a dia como DBA

Subject: I'm Glad You Got Inspired
Posted by: bradmcgehee (view profile)
Posted on: Monday, November 30, 2009 at 10:35 PM
Message: I like what you say here:

"Brad's book is an excellent example of recommended reading for changing the way you think and work. I admit that, the first time I read it, I thought many of Brad's suggestions were, quite frankly, utopian. But it turns out that the 'utopia' is actually a realizable goal."

You are not the first person to think my suggestions were "utopian". Thanks for proving them wrong.

Aloha,
Brad
www.bradmcgehee.com

Subject: Powershell Is Great
Posted by: Barbosa (view profile)
Posted on: Tuesday, December 01, 2009 at 4:15 AM
Message: Very very nice.
Keep Walking

Subject: Enlightening DBAs.
Posted by: Madhu (not signed in)
Posted on: Tuesday, December 01, 2009 at 5:31 AM
Message: Actually I opened this article just by seeing the word "Exceptional DBA". This is book awesome and transforms one normal DBA to E DBA. I did learn new thing from this article, thanks to Laerte. Expect more like this from him and from every one who wants to becom Exceptional DBA.

Subject: Question
Posted by: Steve (view profile)
Posted on: Tuesday, December 01, 2009 at 6:50 AM
Message: So much of this looks like C# that I am wondering what the differences are between Powershell, which I know little about, and a .NET console application. It seems to me that you could program all of this in VS, so I am just trying to get a grasp on the differences. Thanks!

Subject: Question
Posted by: laerte (view profile)
Posted on: Tuesday, December 01, 2009 at 7:31 AM
Message: "Unlike most shells, which accept and return text, Windows PowerShell is built on top of the .NET Framework common language runtime (CLR) and the .NET Framework, and accepts and returns .NET Framework objects. This fundamental change in the environment brings entirely new tools and methods to the management and configuration of Windows."
MSDN

Hi Steve, Thanks for your comment.

In fact, this code looks like C #, because inevitably I am using the SMO and must loaded it either in C #, VB.NET .. etc.
One of the big differences that I see, out the concept of cmdlets, is characteristic of consistency .
Everything in PowerShell is object and output of a cmdlet is the input of another.
I do not need to create a relatively large code into my function to include conditions or sorts .. etc..
The output of this cmdlet can be the entry of a where-object cmdlet that will make this condition , like the examples.
In this same line I can sort, save to XML, format to table, insert into SQL Table..etc
This way, your code in powershell are much smaller and you can perform complex tasks with a combination of cmdlets.
Do not shoot the merits of C# which is certainly a great and powerful language.
I believe that the best language or shell is one that meets your needs. Powershell for me do that
But there are differences between C#, which is a language. and the Powershell wich is a shell.

If you want to read more about , this is a good start

http://msdn.microsoft.com/en-us/library/aa973757(VS.85).aspx

Subject: Question
Posted by: laerte (view profile)
Posted on: Tuesday, December 01, 2009 at 7:43 AM
Message: "Unlike most shells, which accept and return text, Windows PowerShell is built on top of the .NET Framework common language runtime (CLR) and the .NET Framework, and accepts and returns .NET Framework objects. This fundamental change in the environment brings entirely new tools and methods to the management and configuration of Windows."
MSDN

Hi Steve, Thanks for your comment.

In fact, this code looks like C #, because inevitably I am using the SMO and must loaded it either in C #, VB.NET .. etc.
One of the big differences that I see, out the concept of cmdlets, is characteristic of consistency .
Everything in PowerShell is object and output of a cmdlet is the input of another.
I do not need to create a relatively large code into my function to include conditions or sorts .. etc..
The output of this cmdlet can be the entry of a where-object cmdlet that will make this condition , like the examples.
In this same line I can sort, save to XML, format to table, insert into SQL Table..etc
This way, your code in powershell are much smaller and you can perform complex tasks with a combination of cmdlets.
Do not shoot the merits of C# which is certainly a great and powerful language.
I believe that the best language or shell is one that meets your needs. Powershell for me do that
But there are differences between C#, which is a language. and the Powershell wich is a shell.

If you want to read more about , this is a good start

http://msdn.microsoft.com/en-us/library/aa973757(VS.85).aspx

Subject: Congratulation...
Posted by: Nilton Pinheiro (not signed in)
Posted on: Tuesday, December 01, 2009 at 2:21 PM
Message: Hi Laerte... very nice and usefull article!
This is what mean a exceptional DBA: transform a complex task in simple, easy and usefull task. You are showing how do it using PS.

Go ahead with your good and "exceptional" work. I´m waiting for Pt2 :)

Nilton Pinheiro
www.mcdbabrasil.com.br

Subject: Wonderful Stuff Laerte!
Posted by: Jeffrey Snover (not signed in)
Posted on: Friday, December 04, 2009 at 10:32 PM
Message:

http://blogs.msdn.com/powershell/archive/2009/12/05/new-object-psobject-property-hashtable.aspx

Experiment! Enjoy! Engage!

Jeffrey Snover [MSFT]
Distinguished Engineer
Visit the Windows PowerShell Team blog at: http://blogs.msdn.com/PowerShell
Visit the Windows PowerShell ScriptCenter at: http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

Subject: Thanks
Posted by: laerte (view profile)
Posted on: Saturday, December 05, 2009 at 1:58 AM
Message: Thanks a Lot to your visit, help me to write better codes and nice words into Powershell Blog Jeffrey.

Subject: Thaks
Posted by: laerte (view profile)
Posted on: Saturday, December 05, 2009 at 3:58 AM
Message: Brad, your lessons are great. The book is perfect. Thank you for visiting the comment.

Subject: Powershell Script Help
Posted by: SQLHacker (not signed in)
Posted on: Tuesday, December 15, 2009 at 3:03 AM
Message: Hi Laerte,

I have more than 400 servers and one job is scheduled in each server to collect file growth. Now I would like to collect all db growth information from all servers and put them in a dashboard. I thought of giving a try to Powershell. Once I collect them and put into a single database, then I can use reporting services to display them. Could you pls guide me here.

Subject: Powershell Script Help
Posted by: laerte (view profile)
Posted on: Tuesday, December 15, 2009 at 4:19 AM
Message: Hi SQLHacker,

I believe that Powershell can help a lot in this case. I use a monitor similar to tables (http://www.simple-talk.com/community/blogs/laerte/archive/2009/09/29/75024.aspx), but we can adapt easily to databases.
First we have to find the best way to calculate this growth (sp_databases, sp_spaceused, SMO). Please, send an email and we can think of a solution that fits your needs. (laertejuniordba@hotmail.com)

Subject: Great stuff
Posted by: rodolforoim (view profile)
Posted on: Wednesday, January 06, 2010 at 2:01 PM
Message: Laerte, congrats! This is a very good content even for a newbie like me. I'm looking for more PowerShell content and the capabilities of POSH ( ;) ) and I'd really like to see more of these!

Regards,
Rodolfo Roim[MSFT]
MVP Lead for Brazil

Subject: Tks Rodolfo
Posted by: laerte (view profile)
Posted on: Wednesday, January 06, 2010 at 2:22 PM
Message: Thanks a Lot for your visit and comments Rodolfo. I'm glad that you're more a fan of Powershell. It is an active community and is very helpful. You will find many excellent contents and experts who are always ready to help. And what I can help you can count on me

Subject: Help
Posted by: Chen (view profile)
Posted on: Tuesday, May 03, 2011 at 12:36 PM
Message: Laerte,

This is a very good article. I had some issue when following your instruction to learn using PowerShell to manage SQL Server.
if I start PowerShell from SSMS (2008), Get-PSSnapin is missing, also I do not know where I should put Microsoft.PowerShell_profile.ps1

if I start PowerShell from windows (run, type powershell), i will not have invoke-sqlcmd cmdlet.

Can you give me some advice ?

Thanks a lot for the help

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.