Every so often I run into a fellow DBA or database developer who isn't crystal clear on how file/folder and share permissions play together. If you're an MCSE or an experienced system administrator, this should be old hat for you. However, given that I've come across it enough, I thought it might make for a good blog post.

File/Folder Permissions

First, separate file/folder permissions from share permissions. We'll bring them back together in a moment. Let's just start with file/folder permissions. And we'll keep it simple. The ones that often come into play are the following:

• Read - Allows you to read the contents of the file or folder. If you are talking about a file, this allows you to open up the file or copy the file. If you are talking about a folder, it allows you to see the contents of that folder.
• Write  - Again, as the name implies, it allows you to write to the file or folder. Note that Write alone doesn't give delete permissions, but it might as well. There's nothing stopping you from writing a file that is empty, thereby destroying the previous contents.
• Modify - Allows read, write, and delete.
• Full Control - Same as modify + the ability to assign permissions.

Most of the time you're only looking to use read or write in a ETL task or the like. Full control should almost never be needed. You don't usually want your process modifying permissions on files and folders. Now, once we understand the basic permissions, let's talk about how Windows takes those permissions to determine what a user actually has. Unless there is an explicit DENY (like in SQL Server), the operating system aggregates the permissions. For instance, assume user John is a member of the following security groups ETL Users and Support. The permissions assigned to these groups are:

• John - none are explicitly assigned because the best practice is to use security groups wherever possible.
• ETL Users - Read permissions on the folder.
• Support - Write permissions on the folder.

When we put those together, John will have both read and write permissions since the permissions are aggregated. Now, let's say that John is placed in another group, called DBAs. And DBAs have full control. Again, when we aggregate the permissions, John will end up with full control because it comprises read, write, and more. Let's take it one more step and say John is placed in the Developers group. Now the Developers group has a DENY on write. So if we look at all the permissions, we get:

John will have all the permissions given to full control EXCEPT write permissions. The operating system and SQL Server treat the DENY permission the same: it acts as a trump, regardless of the other permissions.

There is one other thing I need to speak on here and that's the concept of inheritance. A file in a folder or a subfolder will, by default, inherit the permissions of the parent folder. You can add to these permissions, but if you want to continue inheriting permissions, you can't take any permissions away (though you could use DENY). So if the parent folder gives a group Full Control but on the subfolder you want the group to only have Read, inheritance isn't going to work. With that said, it is possible to break inheritance. And what this does is allow you to set whatever permissions you want on that file or subfolder, regardless of what the permissions are on the parent folder. However, you must then set and maintain the permissions manually. For instance, if I have a folder called C:\ETL where the security group ETL processes has Modify permissions (the ability to read, write, and delete files) but there is a subfolder C:\ETL\archive where I only want them to have Read permissions, I will have to break the inheritance on C:\ETL\archive and then I will have to set the permissions I want manually.

Share Permissions

Now that we've got the basics of file/folder permissions, let's look at share permissions. Share permissions are like file/folder permissions in that they aggregate and they handle DENY permissions in the same way. With respect to share permissions, there are only a few of them and they are rather straight-forward:

• Read - Allows you to read the contents on the share.
• Change - Allows you to read, write, and delete contents on the share.
• Full control - Change + the ability to modify permissions.

I won't go through an example since share permissions function the same way as File/Folder permissions.

Putting the Two Together

If the file/folder access is happening on the same computer, not going through the share, then only file/folder permissions apply. For instance, if I'm looking to read C:\temp\someDoc.txt on my local system, only file/folder permissions are in effect. I'm not going through a share, so the operating system won't consider the share permissions. But once I go through a share, such as if C:\ETL had a share on it, ETL$, and I access files through it, then both sets of permissions come into play. Here's how the operating system resolves what the effective set of permissions are:

1. It aggregates the file/folder permissions for the resource (the file or folder) being accessed.
2. It aggregates the share permissions for the share.
3. It compares the two sets of permissions and takes the MOST restrictive.

So if I have Change permissions on the ETL$ share (which exposes C:\ETL from the example above), but I only have Read file/folder permissions on the archive subfolder (C:\ETL\archive - see the explanation of inheritance above) that I'm accessing through that share, the most restrictive permission is Read. So when accessing the archive subfolder, I only have Read permissions. At the root of the share, which corresponds to C:\ETL (again, see the explanation of inheritance above), I have Modify permissions. Modify is effectively the same as Change, so I would have the ability to read, write, and delete files at that root level.

Another example is where a user has Full control permissions at the file/folder level but has Read permissions at the share. If the user is accessing a file on the computer locally, then the user can do anything he or she wants. But if the user comes through the share, the user can only read the file. That's because the share permissions are most restrictive.

And there you have it, a quick primer on file/folder/share permissions.