How to Set File Permissions with CACLS and Excel

A VBScript for Setting File Permissions with CACLSCacls Example script excel

This page features the second and third parts of our mission to create user’s home folders and employ CACLS to set the permissions.

CACLS is a command-line program ideal for scripting bulk changes to folders permissions.  What makes this CACLS example difficult, but realistic, is that it also creates the users folders from data stored in an Excel Spreadsheet.

Topics for Creating Folders and Setting Permissions with CACLS

This page deals with Stage 2 and 3 creating the home folders and setting the permissions with CACLS.  If you are not familiar with CACLS, here is a refresher for CACLS switches.

 ♦

Our Mission and Goal

To recap, our mission is to create home directories for users, then assign permissions with CACLS.  The typical structure on a file server would be a shared folder called home, then each user has their own folder as a sub-directory under home.

If you create users home folders with the Active Directory Users and Computers then you can invoke the %username% variable, which not only creates a folder named after the user, but also sets the permission to username full control.

The problem arises if you bulk create users with CSVDE or VBScript, in such cases I find that %username% does not work, so we need an alternative method to create the home folders and set the permissions – a job for CACLS.

As ever the secret of scripting is to build up in stages. 
Stage 1: Introduction to CACLS. Simple example to set folder permission to Administrators full control.
Stage 2: Create the users (sub) folders.  Assumption we have the usernames in a spreadsheet.
Stage 3: Set the permissions on each user’s folder to username: f and administrators: f.  (f= full control)

Stage 2: Create the users (sub) folders

This stage relies on VBScript creating a FileSystemObject (objFSO), which can read the spreadsheet containing the usernames for the folders.  Once we have the objFSO, we pass the spreadsheet cell values to a variable called strUser.  The Do Until… Loop structure cycles through each cell, reading the usernames in the first column.

Stage 3: Set the permissions on the user’s folder

VBScript uses a sub routine to create the folder with the correct name and importantly, set the correct permissions with CACLS commands.  What we want is for each user to have full control of only their own folder.  Administrators are also assigned full control, but to everyone’s folder.  I have to admit that Stage 3 is an extension of Stage 1, both deal with setting the CACLS permissions.

Guy Recommends: Permissions Analyzer – Free Active Directory ToolFree Permissions Analyzer for Active Directory

I like thePermissions Monitor because it enables me to see quickly WHO has permissions to do WHAT.  When you launch this tool it analyzes a users effective NTFS permissions for a specific file or folder, takes into account network share access, then displays the results in a nifty desktop dashboard!

Think of all the frustration that this free utility saves when you are troubleshooting authorization problems for users access to a resource.  Give this permissions monitor a try – it’s free!

Download Permissions Analyser – Free Active Directory Tool

Prerequisites

You must have a server with a shared folder.  This is a script that will execute equally well on a Windows server or an XP machine.  Should you get permission errors, I recommend that you logon as administrator.

Instructions for Creating your Cacls VBScript

  1. Copy and paste the example script below into notepad or a VBScript editor.
  2. Change the value for strHomeFolder, especially the server name.
  3. Save the file with a .vbs extension, for example: Cacls.vbs 
  4. Double click Cacls.vbs and check the permissions with Windows Explorer for strHomeFolder.

Sample Script to Set CACLS permissions

 

‘ CaclsExcel.vbs
‘ Example VBScript to set Cacls
‘ Version 2.4 – September 2010
‘ ———————————————————‘
Option Explicit
Dim intRow, objExcel, objSheet, strPathExcel
Dim strHomeFolder, strHome, strUser
Dim objFSO, objShell, intRunError

‘ Note you will have to amend the following variables
strHome = "\\grand\home\"
strPathExcel = "E:\Scripts\newUsers.xls"
intRow = 3 ‘ Row 1 contains headings

‘ Open the Excel spreadsheet
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open(strPathExcel)

‘ Create a shell for cmd and CACLS
Set objShell = CreateObject("Wscript.Shell")

‘ Here is the loop that cycles through the cells
Do Until (objExcel.Cells(intRow,1).Value) = ""
strUser = objExcel.Cells(intRow, 1).Value
call HomeDir ‘ I decided to use a subroutine
intRow = intRow + 1
Loop
objExcel.Quit ‘ Clears up Excel

Sub HomeDir()
strHomeFolder = strHome & strUser
If strHomeFolder <> "" Then
If Not objFSO.FolderExists(strHomeFolder) Then
On Error Resume Next
objFSO.CreateFolder strHomeFolder
     If Err.Number <> 0 Then
     On Error GoTo 0
     Wscript.Echo "Cannot create: " & strHomeFolder
     End If
On Error GoTo 0
End If
     If objFSO.FolderExists(strHomeFolder) Then
     ‘ Assign user permission to home folder.
     intRunError = objShell.Run("%COMSPEC% /c Echo Y| cacls "_
     & strHomeFolder & " /t /c /g Administrators:f "_
     & strUser & ":F", 2, True)
        If intRunError <> 0 Then
        Wscript.Echo "Error assigning permissions for user " _
        & strUser & " to home folder " & strHomeFolder
        End If
    End If
End If
End Sub
objExcel.Quit

WScript.Quit

‘ End of Cacls example VBScript

VBScript Tutorial – Learning Points

Note 1: This VBScript example creates a FileSystemObject controlled by objFSO.  This is a classic file object which we can manipulate to extract excel values from the spreadsheet.

Note 2: Cacls operates in a cmd prompt, therefore, the script creates a shell object objShell.  The Run command calls for comspec rather than cmd.exe.
objShell.Run("%COMSPEC% /c Echo Y.  Note the Echo Y to close the box.

Note 3:  The permissions section is controlled by:
& strHomeFolder & " /t /c /g Administrators:f "_
& strUser & ":F", 2, True)

strHomeFolder is the path we want to change the permissions.
/t means trash the existing permissions.  Remove all permissions and add those specified by /g.
(/c  Tells the script to continue if there is an error).
/g Administrators:F Sets the new permissions for Administrators with full control.  Observe how this command sets the users permission: & strUser & ‘"F:".  The point is that the /g has two qualifiers, the administrators and the strUser.

Note 4: This script contains two sets of error-correcting code, one caters for cases where the folder does not exist, the other section alerts you if VBScript cannot set the permissions.

Review SolarWinds Free CSVDE Export Tool »

Summary of Setting CACLS Permissions using Excel

Cacls is a command-line utility, which manipulates folder and file permissions.  It is particularly suited to scripting, Cacls is ideal for bulk changes to folder permissions, for example, users home folders.   This page gives you examples of reading from an Excel speadsheet.

If you like this page then please share it with your friends

 


See Also

VBScript CACLS   • VBScript CACLS Excel  •VBScript to Create Folders

WMI Examples   • CACLS Commands   • PowerShell Set ACL

Free WMI Monitor  • WMI PowerShell  • Free SolarWinds Permissions Analyzer