# Usage: powershell .\ExportSchemaSqlLogins.ps1 "SERVERNAME" "DATABASE" "C:\YourOutputPath\DATABASE"
Start Script
Set-ExecutionPolicy RemoteSigned
Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function ExportSchema([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
1.0 Sql Server Connection Part
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
1.1 Sql Server Database Connection Part
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
1.3 SMO Scripter part | object
What specific part of SMO makes this task easy?
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
What specific part of SMO makes this task easy?
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $false
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $false
$options.ToFileOnly = $true
$options.AppendToFile = $true
$options.ScriptDrops = $false
Set options for SMO.Scripter
$scr.Options = $options
#=============
Tables
#=============
Foreach ($tb in $db.Tables)
{
$options.FileName = $scriptpath + "\Tables$($tb).sql"
New-Item $options.FileName -type file -force | Out-Null
If ($tb.IsSystemObject -eq $FALSE)
{
$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
$smoObjects.Add($tb.Urn)
$scr.Script($smoObjects)
}
}
#=============
Views
#=============
$views = $db.Views | where {$_.IsSystemObject -eq $false}
Foreach ($view in $views)
{
$options.FileName = $scriptpath + "\Views$($view).sql"
New-Item $options.FileName -type file -force | Out-Null
if ($views -ne $null)
{
$scr.Script($view)
}
}
#=============
StoredProcedures
#=============
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
Foreach ($StoredProcedure in $StoredProcedures)
{
$options.FileName = $scriptpath + "\StoredProcedures$($StoredProcedure).sql"
New-Item $options.FileName -type file -force | Out-Null
if ($StoredProcedures -ne $null)
{
$scr.Script($StoredProcedure)
}
}
#=============
Functions
#=============
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
Foreach ($function in $UserDefinedFunctions)
{
$options.FileName = $scriptpath + "\Functions$($function).sql"
New-Item $options.FileName -type file -force | Out-Null
if ($UserDefinedFunctions -ne $null)
{
$scr.Script($function)
}
}
#=============
DBTriggers
#=============
$DBTriggers = $db.Triggers
foreach ($trigger in $db.triggers)
{
$options.FileName = $scriptpath + "\Db_Triggers$($trigger).sql"
New-Item $options.FileName -type file -force | Out-Null
if ($DBTriggers -ne $null)
{
$scr.Script($DBTriggers)
}
}
#=============
Table Triggers
#=============
Foreach ($tb in $db.Tables)
{
if($tb.triggers -ne $null)
{
foreach ($trigger in $tb.triggers)
{
$options.FileName = $scriptpath + "\Table_Triggers$($tb)_$($trigger).sql"
New-Item $options.FileName -type file -force | Out-Null
$scr.Script($trigger)
}
}
}
}
#$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
#$conn.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASE;User ID=sqlusername;Password=sqluserpass;MultipleActiveResultSets=True;Application Name=Powershell"
#ExportSchema "SERVERNAME" "DATABASE" "E:\Script_Files\DATABASE"
function ExportSQLLogins{
[cmdletbinding()]
param([parameter(Mandatory=$true)][string] $source
,[string] $ApplyTo
,[string[]] $logins
,[string] $outputpath) #=([Environment]::GetFolderPath("MyDocuments" ))
#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#create initial SMO object
$smosource = new-object ('Microsoft.SqlServer.Management.Smo.Server') $source
#Make sure we script out the SID
$so = new-object microsoft.sqlserver.management.smo.scriptingoptions
$so.LoginSid = $true
#set output filename
#$filename = $source.Replace('/','') + '' + (Get-Date -Format 'yyyyMMddHHmm') + '_logins.sql'
$filename = $source + '_logins.sql'
$outfile = Join-Path -Path $outputpath -ChildPath $filename
#If no logins explicitly declared, assume all non-system logins
if(!($logins)){
$logins = ($smosource.Logins | Where-Object {$_.IsSystemObject -eq $false}).Name.Trim()
}
foreach($loginname in $logins){
#get login object
$login = $smosource.Logins[$loginname]
#Script out the login, remove the "DISABLE" statement included by the .Script() method
$lscript = $login.Script($so) | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
$lscript = $lscript -join ' '
#If SQL Login, sort password, insert into script
if($login.LoginType -eq 'SqlLogin'){
$sql = "SELECT convert(varbinary(256),password_hash) as hashedpass FROM sys.sql_logins where name='"+$loginname+"'"
$hashedpass = ($smosource.databases['tempdb'].ExecuteWithResults($sql)).Tables.hashedpass
$passtring = ConvertTo-SQLHashString $hashedpass
$rndpw = $lscript.Substring($lscript.IndexOf('PASSWORD'),$lscript.IndexOf(', SID')-$lscript.IndexOf('PASSWORD'))
$comment = $lscript.Substring($lscript.IndexOf('/*'),$lscript.IndexOf('*/')-$lscript.IndexOf('/*')+2)
$lscript = $lscript.Replace($comment,'')
$lscript = $lscript.Replace($rndpw,"PASSWORD = $passtring HASHED")
}
#script login to out file
$lscript | Out-File -Append -FilePath $outfile
#if ApplyTo is specified, execute the login creation on the ApplyTo instance
If($ApplyTo){
$smotarget = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ApplyTo
if(!($smotarget.logins.name -contains $loginname)){
$smotarget.Databases['tempdb'].ExecuteNonQuery($lscript)
$outmsg='Login ' + $login.name + ' created.'
}
else{
$outmsg='Login ' + $login.name + ' skipped, already exists on target.'
}
Write-Verbose $outmsg
}
}
}
function ConvertTo-SQLHashString{
param([parameter(Mandatory=$true)] $binhash)
$outstring = '0x'
$binhash | ForEach-Object {$outstring += ('{0:X}' -f $_).PadLeft(2, '0')}
return $outstring
}
#Create a SQL Script in My Documents for all logins
#Export-SQLLogins -source 'SQL'
#=============
Execute
#=============
ExportSchema $args[0] $args[1] $args[2]
ExportSQLLogins -source $args[0] -outputpath $args[2]
