How to search SQL Server Agent Command Text for a string

Question: I'd like to find all SQL Server Agent Jobs with a certain string within the Command section . There's been a change in reference to a database table, therefore I need to review all SQL Server Agent Jobs and check they are not referencing this database table. Is there a sql query I can use to search for the string? As an extra request - do you have any advice on how to run this query across multiple SQL Server Instances and create a report? Answer: Both of these tasks are possible . Firstly , let's look at t-sql... Read more →

Get-ADGroup find AD groups with like

Question: How can I find a list of AD groups with a specific search filter on the name column. ? In this case I want to find all AD groups in the default OU group with a search string. Answer: Assuming you are in the current Domain group \ OU group , find an example of Get-ADGroup which will filter on the search string "admin" Get-ADGroup -Filter "name -like '*admin*'" -Properties * | select name if you need to commit the Get_ADgroup search onto another server - than you'll need to use the -server switch Get-ADGroup -Filter "name -like '*admin'"... Read more →

Automate SQL Server Vulnerability Assessment

Powershell SQLVulnerabilityScan cmdlet is a great addition to the Powershell cmdlet libraries. Most of you know it's available through Microsoft Azure - using the interactive Advanced security pane . The powershell cmdlet - SQLVulnerabilityScan - is available through the SQL Server modules , the SMO and a bunch of new Powershell cmdlets. It's currently in Public Preview. The first step is to check that you can execute the cmdlet , point to a SQL Server and extract the report from the Vulnerability Scan. This powershell snippet - connects to a SQL Server and runs the scan over a specific database... Read more →

How to create a PSCredential object

Creating a Credential Object is useful when you require to pass a username and password through Powershell commands. You might see Credential used in various Powershell cmdlets - such as SqlVulnerabilityAssessmentScan. An example of invoking the SqlVulnerabilityAssessmentScan may be something like: Invoke-SqlVulnerabilityAssessmentScan -Credential "sa" -ServerInstance myserver\inst1 -DatabaseName "master" The problem with the way Credential is used above - will create a request for the password. What might be more efficient is to create a secure object with username \ password Enter the PSCredential. A PSCredential object represents a set of security credentials, i.e username and password. Here is an example... Read more →

Email separator in Powershell

Question: I'm trying to send an email with multiple recipients in Powershell, and getting an exception error message: Exception calling "Add" with "1" argument(s): "An invalid character was found in the mail header: ';'." When I revert to send the email to one recipient - the email is successfull . What is the correct way to pass multiple recipients for powershell email. Some more information that may assist in troubleshooting. 1) When I hardcode the email addresses in the Powershell code e.g $emailTo = "user1@org.com,user2@org.com" it works ok . 2)When I attempt to pass the values in as input parameters... Read more →