Tom Collins answers a question:
How can I obtain a list of BUILTIN\Administrators members using Powershell ?
Read on for the answer and three separate examples.
Comments closedCategory: Powershell
Steve Jones adds 12 years in Powershell:
I saw a fun post on Twitter recently asking days until retirement. I wrote this code:
DECLARE @YearsToRetire INT = 11;SELECT DATEDIFF (DAY, GETDATE (), (DATEADD (YEAR, @YearsToRetire, GETDATE ())));I thought that wasn’t bad, but then I wondered, how would I do this in PowerShell? I knew there had to be a way, so I googled and ran into this article.
Normally I need to take off my shoes to add that many years.
Comments closedRobert Cain shows us one method of working with classes in Powershell:
For this post I’ll begin a series on the use of
PSCustomObject. Prior to the addition of classes in PowerShell 5.0, this was the technique needed to create your own customized objects. It still has a lot of validity today though, as you can use these techniques to extend the objects other people defined, including those already built into PowerShell.In addition, understanding the use of
PSCustomObjectwill give you a better understanding of the way classes work.
Click through to see how you can create an object and assign properties to it, though methods will come in the next post.
Comments closedJeff Hill shows off another feature of dbatools:
If you’ve been responsible for an instance of SQL Server for any length of time you have probably dealt with
sp_configureto change configuration settings at the server level. I have been using SQL Server since v6.5 and it was a thing then too. This is not a post about what the settings are or what they should be set to. There are plenty of resources out there for both. This is about how to see and set these options.
Click through for the process and what you can do with it.
Comments closedAndrew Pruski learns how to run Powershell in Vim so that he doesn’t have to admit he can’t quit it:
I’ve been mucking about with Vim a bit recently and recently found myself (for reasons unknown tbh) writing powershell scripts in it.
Once I’d written a script, I would exit Vim to run it…however…that got me thinking, can I run powershell scripts directly in Vim?
Wait, vim on Windows? The madman!
Comments closedJeffrey Hicks has a two-parter on discovering Pester tags. Part one is Jeffrey’s take:
As I resolved at the end of last year, I am doing more with Pester in 2022. I’m getting a bit more comfortable with Pester 5 and as my tests grow in complexity I am embracing the use of tags. You can add tags to different Pester test elements. Then when you invoke a Pester test, you can filter and only run specific tests by their tag. As I was working, I realized it would be helpful to be able to identify all of the tags in a test script. After a bit of work, I came up with a PowerShell function.
Yesterday I shared some PowerShell code I wrote to discover tags in a Pester test. It works nicely and I have no reason to complain. But as usual, there is never simply one way to do something in PowerShell. I got a suggestion from @FrodeFlaten on Twitter on an approach using the new configuration object in Pester 5.2. I’ll readily admit that I am still getting up to speed on the latest version of Pester. That’s one of my goals for this year, so this was a great chance to learn something new.
Click through to see how both approaches work.
Comments closedJeffrey Hicks builds a useful snippet:
During this process, I decided I needed to help myself speed up the test writing phase. I have a standard set of tests that I like to use for functions in my module. But copying and pasting code snippets is tedious. I know I could create a set of VS Code snippets, but that feels limiting and I’d have to make sure the snippets are available on all systems where I might be running VS Code. Instead, I wrote a PowerShell function to accelerate developing Pester 5.x tests.
My function takes a module and extracts all of the public exported functions. For each function, it creates a set of standard Pester assertions. These are the baseline or boilerplate tests that I always want to run for each function. Each function is wrapped in a Describe block. Although, I can opt for a Context block instead. This command will also insert tags. Note that my code for the tag insertion relies on the ternary operator from PowerShell 7.
Click through for the code.
Comments closedRayis Imayev troubleshoots an Azure Data Factory deployment:
To run these scripts, you will need to have Azure PowerShell installed on your DevOps agents (Azure Pipeline Agents). If your pipeline agents are Microsoft-hosted, then you’re good and all maintenance and software updates are taken care of for you. However, when you implement and install self-hosted agents, then additional software and component installation is solely your responsibility to maintain.
Recently, while I was configuring those pre- and post-deployment scripts for my Azure Data Factory deployment, I received the following error message, “Could not find the modules: ‘Az.Accounts’ with Version: ””.
Rayis does have a working solution, but I do recommend against installing modules in System32 because that directory is supposed to be reserved for Windows. Instead, for multi-user Powerhsell modules, I’d drop them in %ProgramFiles%\WindowsPowerShell\Modules instead, following the general Powershell guidance.
David Alcock wants to move a login:
Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.
With domain accounts that’s pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it’s just a case of recreating the account on the new instance.
SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn’t know what the password should be. It’s worth saying at this point that the preference should always be to use domain accounts, they’re more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let’s proceed with the scenario of recreating a SQL authentication login with an unknown password, and we’ll need to get creative.
And as you’d expect, dbatools makes a dramatic appearance.
Comments closedTom Collins has an answer for us:
Do you have a powershell script or t-sql script to find if ssl encryption is enabled on SQL Server? I don’t want to check if connections are SSL enabled but actually check the Force Encryption flag is enabled
Click through for two answers, one in T-SQL and one in Powershell.
Comments closed