Good evening all!

Nice new shiny theme on the blog, I got a little tired of the old one,

I’ve been working on some PowerShell recently and I thought I would share with you all. In my job I was presented with a huge job, to restructure our ancient file server and migrate our structure into a DFS share (which brings a lot of nice toys I won’t need to talk about here)

I took the approach of ADGLP and structured role groups to grant permissions to local domain groups in AD. A much neater approach to managing file permissions, end user changes role? Just change their AD group to reflect their new role and their file permissions will follow suit.

Now we also have to evidence that we have audited our new file share as one of our ISO27001 compliance policies and resolved any issues with permissions. I hadn’t gotten around to solving this requirement until this afternoon in the office.

I’ve been trialling writing output data from my Powershell scripts into an SQL database and I thought, with a file server with a scale like ours, SQL would be an ideal place to store the data in, we can query it and manipulate the data to get exactly what we need and for my manager – utilize SQL server reporting services to make some pretty web based tables and graphs.

I wrote the script up and based it on a PowerShell snippet a colleague wrote up a few years ago (Click Here if you’re interested) and made it to save variables to then be pushed into SQL.

Before the script is run, you’ll need an SQL Server to host the database and a service account to grant the relevant permissions to, remember. That service account will need full access on the file share you’re scanning.

Create a database with a good name, no dashes or anything, In my example I have created a database called FilePermissions.

Once this database has been created, you’ll need to create the tables within the database for the script to insert data into. to do so, open a new query window on the new database and type :

CREATE TABLE permissions ( ID INT IDENTITY(1,1) NOT NULL, Folderpath nvarchar(MAX) NOT NULL, IdentityReference nvarchar(MAX) NOT NULL, AccessControlType nvarchar(MAX) NOT NULL, Isinherited nvarchar(MAX) NOT NULL, InheritanceFlags nvarchar(MAX) NOT NULL, PropagationFlags nvarchar(MAX) NOT NULL, timestamp DATETIME2 NOT NULL, PRIMARY KEY (ID) );

Press F5 to execute the statement, you should then see the table and columns appear in the management studio. To see all data in the database, run a “select * from permissions” query.

Once you’re happy with the DB side of things, you can move onto the script in PowerShell itself,

The full file is available from GitHub for download.

Edit the variables at the top of your script to suit your environment

$dbservername = "PC01390" $databasename = "FilePermissions" $timestamp = Get-Date $RootPath = "D:\"

The database server name will be the DB server itself, DB name is well – the database name you created earlier.

Leave the timestamp var as it is, it’s for one of the columns in the database.

Rootpath is the path you wish to have the folders scanned. By default the script will scan the top 3 levels of folders. You can change this by adding or removing wildcards from this line of code :

$Folders = dir $RootPath\*\*\*\

Once you have edited the script, you’re ready to rock and roll!

Place the ps1 file on the relevant server or client machine and launch the script. You will then see an output of a “1” if it has written an entry into the database successfully, else it will show a red error with the information on the screen.

Once the script has finished running, head into SQL server management studio and you’ll see the database has now been populated with lovely, useful data.

I hope this script helps you as much as it helped me! Feel free to raise an issue on github or a comment on here if you hit any problems.

Bye for now!

-Ainsey11