x

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

more ▼

asked Aug 12, 2015 at 10:49 AM in Default

avatar image

edric.howard
40 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Aug 13, 2015 at 02:40 PM

avatar image

edric.howard
40 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x122
x67
x10
x8

asked: Aug 12, 2015 at 10:49 AM

Seen: 117 times

Last Updated: Aug 13, 2015 at 02:47 PM

Copyright 2017 Redgate Software. Privacy Policy