This function returns information about SQL SERVER LOGINS.

You see for example, which logins were created in X days.

The Save-SQLMsg its only to log the errors (some server out for example). But It can easily removed ou changed.

Examples :

#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
Edit|Remove
Function Save-SQLMsg () 
<#
----------------------------------------------------------
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 
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:\temp1",
		[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 Get-SQLLogin()
<#
----------------------------------------------------------
Returns information about logins
----------------------------------------------------------
Requires POWERSHELL 2.0

$TXTServersList         = Txt with servers to be checked - Default server


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

	begin 
	{
		[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
	}
	Process
	{
	
		$verbosePreference="continue" 
		[datetime] $TodayDate = get-date -Format "yyyy/MM/dd hh:mm:ss"
		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"
				$Data = $Server.logins | where-object {$_.State -eq "Existing"} | foreach {
				
					$Object = New-Object PSObject	
					
					[datetime] $CreateDate = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.CreateDate  
					[datetime] $DateLastModified = "{0:yyyy/MM/dd hh:mm:ss}" -f [datetime] $_.DateLastModified 
					
					
	
					$Object | add-member Noteproperty LineNumber  					$LineNumber 	
					$Object | add-member Noteproperty Date  						$TodayDate 	
					$Object | add-member Noteproperty ServerName  					$svr
					$Object | add-member Noteproperty LoginName		 				$_.Name
					$Object | add-member Noteproperty CreateDate 					$CreateDate 
					$Object | add-member Noteproperty DateLastModified 				$DateLastModified 
					$Object | add-member Noteproperty LoginType			 			$_.LoginType					
					$Object | add-member Noteproperty AsymmetricKeys	 			$_.AsymmetricKeys
					$Object | add-member Noteproperty Certificate 					$_.Certificate
					$Object | add-member Noteproperty DefaultDatabase				$_.DefaultDatabase
					$Object | add-member Noteproperty DenyWindowsLogin 				$_.DenyWindowsLogin
					$Object | add-member Noteproperty HasAccess						$_.HasAccess
					$Object | add-member Noteproperty IsDisable						$_.IsDisable					
					$Object | add-member Noteproperty IsLocked						$_.IsLocked
					$Object | add-member Noteproperty IsPassowordExpired			$_.IsPassowordExpired					
					$Object | add-member Noteproperty IsSystemObject 				$_.IsSystemObject					
					$Object | add-member Noteproperty Language						$_.Language
					$Object | add-member Noteproperty LanguageAlias					$_.LanguageAlias
					$Object | add-member Noteproperty MustChangePassword			$_.MustChangePassword
					$Object | add-member Noteproperty PasswordExpirationEnabled		$_.PasswordExpirationEnabled					
					$Object | add-member Noteproperty PasswordPolicyEnforced		$_.PasswordPolicyEnforced

			
					$FinalResult += $Object
					$LineNumber ++ 
				} 

															
			
			}	catch {		
						$msg = $error[0]
						Write-Warning $msg
						Save-SQLMsg "Get-SQLLogin" "$svr" "" "$msg" 
						continue
			} 
			
		}
		
		Write-Output $FinalResult			
	}	

}