T-SQL Tuesday #39 – Set file permissions with PowerShell

Wayne Sheffield (blog|twitter) is hosting this month’s T-SQL Tuesday and the subject is “Can you shell what the PoSH is Cooking“. More specifically he want that we blog about anything PowerShell and SQL Server related.

As a DBA I use PowerShell for some automation tasks such as: reading error logs, scripting SQL server objects, comparing data in tables etc. Today I will write about script I wrote for setting read file permission on SQL Server trace files.

From SQL Server 2005 onwards, when you run server side trace in SQL server, a new trace file will be created and the permission for this trace file is set only to SQL Service account. The permissions of the directory are not inherited.

Because of that SQL Server security policy, I find myself in a situations that I have hundreds of trace files on the server share, but I cannot access them with my user. So I decided to write a PowerShell script to give my user read access to all trace files in specified folder.

$path = "D:\Folder\"
$user = "domain\username"
$SystemRights = "Read"
$AccessType = "Allow"
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($user, $SystemRights, $AccessType)
$files = Get-ChildItem $path
foreach($file in $files)
{
$acl = (Get-Item $file).GetAccessControl("Access")
$acl.AddAccessRule($AccessRule)
$filepath = $path +""+ $file.name
Set-Acl $filepath $acl
} 

About these ads