Subscribe to this blog
Briefcase
Print
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
{
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.
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