asked
Using regex with powershell to search permissions?
Hey Folks, I have written a powershell script that searches my local SQL Server DB project for permissions: GRANT EXECUTE ON OBJECT::[dbo].[PROC_NAME] TO [USER]; I have written a powershell script which successfully searches the two lines independently. I am now trying to use regex to hit both lines in one search. According to RegExr searching for the following should work: GRANT EXECUTE\s*ON OBJECT::\[dbo\]\.\[.*\] TO \[.*\] Unfortunately once I have transferred this search into powershell it does not work, can anyone assist? Here's the full search: #Set variables $Location = "DB Project Location" Get-ChildItem $Location -include *.sql -recurse | #Where-Object {(Select-String -InputObject $_ -pattern "GRANT EXECUTE")} | #Where-Object {(Select-String -InputObject $_ -pattern "\s*ON OBJECT::\[dbo\]\.\[.*\] TO \[.*\]")} Where-Object {(Select-String -InputObject $_ -pattern "GRANT EXECUTE\s*ON OBJECT::\[dbo\]\.\[.*\] TO \[.*\]")} I have tried replacing \s with \W and even a combination \W\s*, \W*\s* I have also tried \r and \n as well as a combination \r\n
This issue has subsequently been resolved. It seems powershell does not like the use of wildcards over multiple lines so setting a variable to hold the contents and then searching the variable works well.
16 People are following this question.
