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 →

Performance impact of TDE

Question: What is the performance impact of enabling Transparent Data Encryption (TDE) on SQL Server database query performance? How can I measure the performance impact ? Answer: There is a general outline about the pros and cons of different Encryption on Encrypting data in SQL Server . So once you\ or your organisation has made the decision to enable TDE for SQL Server databases . it's time to assess the impact : performance , volume and DBA management overhead. According to the Microsoft documentation "TDE has an estimated performance impact around 3-5% and can be much lower if most of... 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 →