PowerUpSQL

1.103.8

templates/CheatSheet_SMO_Commands.ps1

                                # Script Name:

#   SQL Server SMO Cheatsheet (0.CheatSheet-SqlServerSmo.ps1)

# Author:

#   Scott Sutherland (@_nullbind), 2015 NetSPI

# Description:  

#   This file contains basic examples that show how to query SQL Server

#   for configuration information using the SQL Server SDK SMO APIs.

# Requirements:  

#   The examples in this cheatsheet require two SMO libraries that get installed with SQL Server.  

#   The file names have been listed below:

#   - Microsoft.SqlServer.Smo.dll

#   - Microsoft.SqlServer.SmoExtended.dll

# References:

#   https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx

# Import SMO Libs - required for all examples below

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")| Out-Null

# Authenticate - Integrated Windows Auth - works

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "server\instance" 

# Get instance option

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

# Authenticate - SQL Server authentication - mixed mode - works

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "10.1.1.1"

$srv.ConnectionContext.LoginSecure=$false; 

$srv.ConnectionContext.set_Login("user"); 

$srv.ConnectionContext.set_Password("password")  

$srv.Information

# Get version / server information

$srv.Information

$srv.Name

$srv.NetName

$srv.ComputerNamePhysicalNetBIOS

$srv.Version

$srv.VersionMajor

$srv.VersionMinor

$srv.Edition

$srv.EngineEdition

$srv.OSVersion

$srv.DomainInstanceName

$srv.DomainName

$srv.SqlDomainGroup

# Get service informaiton

$srv.ServiceName

$srv.ServiceAccount

$srv.ServiceStartMode

$srv.BrowserServiceAccount

# Get state information

$srv.State

$srv.Status

# Get listener information

$srv.NamedPipesEnabled

$srv.TcpEnabled

# Get directory path information

$srv.RootDirectory

$srv.InstallDataDirectory

$srv.InstallSharedDirectory

$srv.ErrorLogPath

$srv.MasterDBLogPath

$srv.MasterDBPath

$srv.BackupDirectory

# Logins, roles, and privilege information

$srv.ConnectionContext

$srv.LoginMode

$srv.Logins

$srv.Roles

$srv.EnumServerPermissions()

# Window accounts / groups assigned logins in SQL Server

$srv.EnumWindowsUserInfo()

$srv.EnumWindowsUserInfo() | select "account name"

$srv.EnumWindowsDomainGroups()

$srv.EnumWindowsGroupInfo("Domain Admins")

# Credentials / proxy_account

$srv.Credentials

$srv.ProxyAccount

# Databse information

$srv.Databases

# cluster / mirror information

$srv.IsClustered

$srv.ClusterName

$srv.EnumClusterMembersState

$srv.EnumClusterSubnets

$srv.EnumDatabaseMirrorWitnessRoles()

# SQL Server settings

$srv.Configuration

$srv.Settings

$srv.Properties

$srv.Mail

$srv.MailProfile

$srv.Triggers

$srv.AuditLevel

$srv.Audits

$srv.LinkedServers

$srv.Endpoints

$srv.JobServer

$srv.EnumServerAttributes()

# SQL Server enumeration

# https://msdn.microsoft.com/en-us/library/ms210366.aspx

$srv.PingSqlServerVersion("server\Standard")

$srv.PingSqlServerVersion("1.1.1.1",'sa','password')

$SQLSvr = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($true); $SQLSvr | Out-GridView