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

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

TSQL2sday

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
Categories: T-SQL Tuesday

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: