Listando Informações de SQL Servers Logins em Powershell


 

Technet Scripting Guys Link

Pessoal, esta função lista as informações do login no SQL Server.
Você pode saber por exemplo quais logins foram criados nos últimos X dias.
A função Save-SQLMsg é colocada junta pois se existir algum problema com o servidor , este é logado.
Pode ser alterada e facilmente removida também

 

Function Get-SQLLogin

  1: Function Save-SQLMsg () 
  2: <#
  3: ----------------------------------------------------------
  4: Save  log in file
  5: ----------------------------------------------------------
  6: Requires POWERSHELL 2.0
  7: 
  8: File Name     			= $NamePS1
  9: Server name   			= $Server 
 10: DatabaseName 			= $databasename
 11: Message To Log		    = $Message
 12: Path to generate file 	= $PathFileLog 
 13: Date to Log				= $TodayDate
 14: #>
 15: 
 16: 
 17: {
 18: 
 19: 	[CmdletBinding()]
 20: 	
 21: 	Param (
 22: 		[Parameter(position=1,Mandatory = $true )][String] $NamePS1,
 23: 		[Parameter(position=2,Mandatory = $true )][String] $Server,
 24: 		[Parameter(position=3,Mandatory = $false )][String] $DatabaseName = "",
 25: 		[Parameter(position=4,Mandatory = $false )][String] $Message = "" ,
 26: 		[Parameter(position=5,Mandatory = $false )][String] $PathFileLog = "C:\temp1",
 27: 		[Parameter(position=6,Mandatory = $false )][String] $TodayDate = (Get-Date -Format "yyyyMMddhhmmss")
 28: 		)
 29: 	process 
 30: 	{
 31: 	
 32: 		#test if path wich will contains the error file exists. if not create 
 33: 	
 34: 	if (!(Test-Path -path $PathFileLog))
 35: 	{
 36: 		try {
 37: 			New-Item $PathFileLog -itemtype directory -ErrorAction  Stop   | Out-Null
 38: 		}
 39: 		catch {
 40: 			Write-Host "Can not create log file path"
 41: 			break;
 42: 		}
 43: 	} 
 44: 	
 45: 	
 46: 	$NameFileFull = $PathFileLog + "\" + $NamePS1 + $TodayDate + ".log" 
 47: 	
 48: 	$TDate = $TodayDate.Substring(0,4) + "-" + $TodayDate.Substring(4,2) + "-" + $TodayDate.Substring(6,2) 
 49: 	
 50: 	"Server : " + $Server + " Database : " + $DatabaseName + " Date : "  + $TDate + " Message: "  + $Message | Out-file  $NameFileFull -append 
 51: 	} 
 52: }
 53: 
 54: Function Get-SQLLogin()
 55: <#
 56: ----------------------------------------------------------
 57: Returns information about logins
 58: ----------------------------------------------------------
 59: Requires POWERSHELL 2.0
 60: 
 61: $TXTServersList         = Txt with servers to be checked - Default server
 62: 
 63: 
 64: #>
 65: {
 66: 	[CmdletBinding()]
 67: 	
 68: 	PARAM	(
 69: 				[Parameter(position=1,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="File Servers List")]
 70: 				[Alias("FullNameTXT")]
 71: 				[String] $TXTServersList = $env:COMPUTERNAME
 72: 				
 73: 			)
 74: 
 75: 	begin 
 76: 	{
 77: 		[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
 78: 	}
 79: 	Process
 80: 	{
 81: 	
 82: 		$verbosePreference="continue" 
 83: 		[datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss"
 84: 		if ($TXTServersList.substring($TXTServersList.length -4,4) -eq ".TXT")
 85: 		{
 86: 			try
 87: 			{
 88: 				$ServersList = get-content $TXTServersList	
 89: 			} catch {
 90: 						$msg = $error[0]
 91: 						Write-Warning $msg
 92: 						break;
 93: 			}
 94: 		}	
 95: 		else
 96: 		{
 97: 			$ServersList = $TXTServersList
 98: 		}	
 99: 		
100: 		
101: 		$LineNumber = 1
102: 		$FinalResult = @()
103: 	
104: 		foreach ($svr in  $ServersList )
105: 		{
106: 			try 
107: 			{
108: 				$Server=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
109: 				$Data = $Server.logins | where-object {$_.State -eq "Existing"} | foreach {
110: 				
111: 					$Object = New-Object PSObject
112: 					
113: 					[datetime] $CreateDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.CreateDate  
114: 					[datetime] $DateLastModified = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.DateLastModified 
115: 					
116: 					
117: 	
118: 					$Object | add-member Noteproperty LineNumber  					$LineNumber 	
119: 					$Object | add-member Noteproperty Date  						$TodayDate 	
120: 					$Object | add-member Noteproperty ServerName  					$svr
121: 					$Object | add-member Noteproperty LoginName		 				$_.Name
122: 					$Object | add-member Noteproperty CreateDate 					$CreateDate 
123: 					$Object | add-member Noteproperty DateLastModified 				$DateLastModified 
124: 					$Object | add-member Noteproperty LoginType			 			$_.LoginType					
125: 					$Object | add-member Noteproperty AsymmetricKeys	 			$_.AsymmetricKeys
126: 					$Object | add-member Noteproperty Certificate 					$_.Certificate
127: 					$Object | add-member Noteproperty DefaultDatabase				$_.DefaultDatabase
128: 					$Object | add-member Noteproperty DenyWindowsLogin 				$_.DenyWindowsLogin
129: 					$Object | add-member Noteproperty HasAccess						$_.HasAccess
130: 					$Object | add-member Noteproperty IsDisable						$_.IsDisable					
131: 					$Object | add-member Noteproperty IsLocked						$_.IsLocked
132: 					$Object | add-member Noteproperty IsPassowordExpired			$_.IsPassowordExpired					
133: 					$Object | add-member Noteproperty IsSystemObject 				$_.IsSystemObject					
134: 					$Object | add-member Noteproperty Language						$_.Language
135: 					$Object | add-member Noteproperty LanguageAlias					$_.LanguageAlias
136: 					$Object | add-member Noteproperty MustChangePassword			$_.MustChangePassword
137: 					$Object | add-member Noteproperty PasswordExpirationEnabled		$_.PasswordExpirationEnabled					
138: 					$Object | add-member Noteproperty PasswordPolicyEnforced		$_.PasswordPolicyEnforced
139: 
140: 			
141: 					$FinalResult += $Object
142: 					$LineNumber ++ 
143: 				} 
144: 
145: 				Write-Output $FinalResult												
146: 			
147: 			}	catch {		
148: 						$msg = $error[0]
149: 						Write-Warning $msg
150: 						Save-SQLMsg "Get-SQLLinkedServer" "$svr" "" "$msg" 
151: 						continue
152: 			} 
153: 			
154: 		}
155: 	}	
156: 
157: }	
158: 

 

 

Download i

Exemplos de uso

#all properties returneb by fucntion
LineNumber               int
Date                     Datetime
ServerName               String
LoginName                String
CreateDate               Datetime
DateLastModified         Datetime
LoginType                String
AsymmetricKeys           String
Certificate              String
DefaultDatabase          String
DenyWindowsLogin         Boolean
HasAccess                Boolean
IsDisable                Boolean
IsLocked                 Boolean
IsPassowordExpired       Boolean
IsSystemObject           Boolean
Language                 String
LanguageAlias             String
MustChangePassword        Boolean
PasswordExpirationEnabled Boolean
PasswordPolicyEnforced    Boolean

List All Logins information Server Default
Get-SQLLinkedServer

List All Logins information All servers (by txt)
Get-SQLLinkedServer "c:\temp\servers.txt"

List All Logins information All servers (by txt) (name and createdate  )
Get-SQLLinkedServer "c:\temp\servers.txt" | select LineNumber,Date,ServerName,LoginName,CreateDate |Format-Table

List All Logins information All servers (by txt) (name and createdate less than 7 days) and stored SQL Server Table
Get-SQLLogin | where-Object {((get-date) – ($_.createdate)).days -lt 7} | select LineNumber,Date,ServerName,LoginName,CreateDate |Format-Table

TSQL create table (Logins XML)

(Get-SQLLinkedServer "c:\temp\servers.txt" | where-Object {((get-date) – ($_.createdate)).days -lt 7} | select LineNumber,Date,ServerName,LoginName,CreateDate | ConvertTo-Xml -NoTypeInformation).save("c:\temp\Logins.xml")
$XML = (Get-Content c:\temp\Logins ) -replace "’", "”"
$SQL = "insert into Logins (XML) values (‘$XML’)"
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "tempdb" -query $sql

TSQL to list with servername  condition = "Jupiter"
Select t2.Col1.value(‘(Property[@Name="LineNumber"]/text())[1]’, ‘int’) LineNumber,
    t2.Col1.value(‘(Property[@Name="Date"]/text())[1]’, ‘nvarchar(255)’) Date,
    t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) ServerName,
    t2.Col1.value(‘(Property[@Name="LoginName"]/text())[1]’, ‘nvarchar(255)’) LoginName,
    t2.Col1.value(‘(Property[@Name="CreateDate"]/text())[1]’, ‘nvarchar(255)’) CreateDate
FROM dbo.Logins
CROSS APPLY xml.nodes(‘/Objects/Object’) As t2(Col1)
where t2.Col1.value(‘(Property[@Name="ServerName"]/text())[1]’, ‘nvarchar(max)’) = ‘Jupiter’

 

POWERSHELL ROCKS !!!!

 

“She gave me the Queen
She gave me the King
She was wheelin’ and dealin’
Just doin’ her thing
She was holdin’ a pair
But I had to try
Her Deuce was wild
But my Ace was high
But how was I to know
That she’d been dealt with before
Said she’d never had a Full House
But I should have known
From the tattoo on her left leg
And the garter on her right
She’d have the card to bring me down
If she played it right

She’s got the jack, she’s got the jack
She’s got the jack, she’s got the jack
She’s got the jack, she’s got the jack
She’s got the jack, she’s got the jack
She’s got the jack, jack, jack, jack, jack, jack, jack
She’s got the jack”

She’s Got The Jack

AC/DC

 

Follow me on Twitter

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.

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