Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Finding Invalid SQL Logins

As many of you know the system stored procedure sp_validatelogins is used for finding invalid logins. Although sp_validatelogins is useful there's one problem -- the output isn't always accurate. You see when you add a a Windows account to SQL Server the SID as well as the domain (or computer name) slash account name are stored in master database, if the account is renamed in Active Directory or in the case of local users on the local system, the account stills retains access to SQL Server. How is this possible? That's because the SID is unchanged and that is what SQL Server uses. When you run sp_validatelogins the account name is validated but not the SID and a valid but rename account is returned.
 
So, what we need to do is make sp_validateLogins accurate by resolving the SID against Active Directory or the local system. As add bonus we should return the rename account name. Fortunately this is pretty easy with a little Powershell script. The following is a standalone excerpt from SQL Server PowerShell Extensions, edited to work with Microsoft's sqlps:
 
function Get-InvalidLogins           
{           
    param($ServerInstance)           
           
    foreach ($r in Invoke-SqlCmd -ServerInstance $ServerInstance -Database 'master' -Query 'sp_validatelogins')           
    {           
        $NTLogin = $r.'NT Login'           
        $SID = new-object security.principal.securityidentifier($r.SID,0)           
        $newAccount = $null           
        trap { $null; continue } $newAccount = $SID.translate([system.security.principal.NTAccount])           
       if ($newAccount -eq $null) {            
        $isOrphaned = $true           
        $isRenamed = $false           
        }           
       else {           
        $isOrphaned = $false           
        $isRenamed = $true           
        }           
        if ($NTLogin -ne $newAccount) {           
        new-object psobject |           
        add-member -pass NoteProperty NTLogin $NTLogin |           
        add-Member -pass NoteProperty TSID $SID |           
        add-Member -pass NoteProperty Server $ServerInstance |           
        add-Member -pass NoteProperty IsOrphaned $isOrphaned |           
        add-Member -pass NoteProperty IsRenamed $isRenamed |           
        add-Member -pass NoteProperty NewNTAccount $newAccount           
        }           
    }           
           
} #Get-InvalidLogins
 
To use the script simply copy and paste the function defintion into a sqlps session or alternatively you can add the function to your Windows Powershell profile.
 
Next simply call the function specifying a SQL Server instance:
 
Get-InvalidLogins "Z002\SQL2K8"

 

Credits and History 

The original idea for the code came from a blog post which uses a CLR solution.  In my pre-Powershell days (2006) I created this Perl script.

Comments

Posted by Anonymous on 12 January 2010

Pingback from  Dew Drop – January 12, 2009 | Alvin Ashcraft's Morning Dew

Posted by Steve Jones on 12 January 2010

Nice one, I hadn't realized the proc doesn't always work, though to be fair I've not really used it.

Posted by Anonymous on 13 January 2010

Daily tech links for .net and related technologies - Jan 13-15, 2010 Web Development Adding Multiple

Posted by eric.simbozel on 19 January 2010

Hi, fine script but, do you have the same for SQL2005

many thank's.

Posted by cmille19 on 19 January 2010

The above script will work against SQL 2000 and 2005 and can be run from any machine with SQL 2008 Management Studio (sqlps). To remove the dependency on 2008, we'll need to create a second function which I call Get-SqlData, available here: chadwickmiller.spaces.live.com/.../cns!EA42395138308430!445.entry

Get-SqlData is similar to Invoke-SqlCmd except that it uses standard ADO.NET available on any machine with Powershell installed. To use copy the function Get-SqlData from my blog post. Next paste or save the defintion into a script file or place in your profile. Finally replace invoke-sqlcmd in the script above with Get-SqlData.

Posted by cmille19 on 19 January 2010

The link to Get-SqlData didn't post correctly, so here's an alternate location:

http://poshcode.org/1139

Posted by Jason Brimhall on 19 January 2010

Thanks Chad.  This script will be a big time saver in an upcoming project.

Posted by Anonymous on 21 May 2010

Pingback from  2007 Highlander Tail Light, Highlander Movies Christopher Lambert Connor Macleod

Posted by Anonymous on 22 May 2010

Pingback from  Radiator 1998 Nissan 240sx Aftermarket, 98 Nissan Maxima Radiator Drain Plug Gasket

Posted by Anonymous on 22 May 2010

Pingback from  1961 Raleigh Gran Sport, Mountaineer Body Parts Grand Marquis

Posted by Anonymous on 22 May 2010

Pingback from  Centurion Radiator Online Oem, 1970 Buick Centurion Parts

Posted by Anonymous on 22 May 2010

Pingback from  D300 Discount These Nikon D3, Performance Nikon D300 Review

Posted by Anonymous on 22 May 2010

Pingback from  1998 Eagle Talon Reliability Recalls, 1993 Eagle Talon Header - 216.an74.com

Posted by Anonymous on 23 May 2010

Pingback from  E 150 Econoline Club Wagon Equipment, E 250 Econoline Club Wagon Download Egr Valve Position Sensor - 0.jordanbrandallamerican.com

Posted by Anonymous on 23 May 2010

Pingback from  Mirada Engine Rv Price, Vehicle Dodge Mirada Radiator Capacity - 487.binggreen.com

Posted by Anonymous on 24 May 2010

Pingback from  1985 Mercury Colony Park, Cheap Colony Park Station Wagon - 383.ja3ra.com

Posted by Anonymous on 24 May 2010

Pingback from  Promo 1981 Pontiac Bonneville, Trans Sport Used Aztek Details Pontiac Bonneville - 216.computeronlinebingo.com

Posted by Anonymous on 24 May 2010

Pingback from  Falcon Part Elementary School, Fifth Element Blu Ray Replacement - 340.myipgirl.com

Posted by Anonymous on 24 May 2010

Pingback from  1979 Parts Rx7 Suspension, Discount 1987 Mazda Rx 7 Engines Rx7 Turbo - 180.tijuanareader.com

Posted by Anonymous on 24 May 2010

Pingback from  1996 Dodge 2500 Towing, X10 K2500 Review 2003 Chevrolet Suburban - 289.cmanager.org

Posted by Anonymous on 24 May 2010

Pingback from  Part 2003 Saturn L200 Fog Light, L200 Collection Sale - 294.luna-atra.net

Posted by Anonymous on 24 May 2010

Pingback from  Clip Telekom Standard, Improve Standard English - 118.ja3ra.com

Posted by Anonymous on 24 May 2010

Pingback from  Antiques 1997 Geo Prizm, Brian Haley Geomar - 383.myipgirl.com

Posted by Anonymous on 24 May 2010

Pingback from  1993 Eagle Talon Edmunds, Eagle Talon Tsi Awd For Sale - 60.zapstreaming.com

Posted by Anonymous on 24 May 2010

Pingback from  Club Relay Games Ball, Ucla Club - 206.akemet.com

Posted by Anonymous on 24 May 2010

Pingback from  2nd Hand Yahoo Autos Mercedes Benz C55 Amg, C55 Amg Repair 8cyl - 4.codebluehacks.org

Posted by Anonymous on 24 May 2010

Pingback from  Ford Pinto Suspension, Radiator 1980 Ford Pinto - 82.tgrconversions.com

Posted by Anonymous on 25 May 2010

Pingback from  Chrysler Pacifica Interior Bucket Seats, 2005 Pacifica Touring Interior - 404.jeepsunlimted.com

Posted by Anonymous on 25 May 2010

Pingback from  Sl300 Coupon Dvd+ Rw, Buy L300 3 Aftermarket Car Truck - 413.akemet.com

Posted by Anonymous on 25 May 2010

Pingback from  Mercedes Benz E550 Parts Vehicle, Dc80 Waterproof Case For Powershot Sd550 - 300.zapstreaming.com

Posted by Anonymous on 25 May 2010

Pingback from  Pb350 Sale 7.0, Pb350 Medium Maple Baseball - 59.animejin.com

Posted by Anonymous on 25 May 2010

Pingback from  Dasher Full New Orleans Bowl, Shoprider Dasher 3 Scooter Swivel Seat - 201.mfbattle.com

Posted by Anonymous on 25 May 2010

Pingback from  Buy Sl2 Floor Stands Bose Lifestyle V30, 1998 Saturn Sl2 Radiator Cap - 428.eumreborn.com

Posted by Anonymous on 25 May 2010

Pingback from  Handleiding Sony Ericsson C510, 1970 Datsun 510 2 - 72.computeronlinebingo.com

Leave a Comment

Please register or log in to leave a comment.