locked
Access to public folder by SQL user RRS feed

  • Question

  • Hello,

    I have some problem with an application which tries to access file system folders on the computer where SQL Server 2008 R2 is running. If I connect with the sql user "sa" I have access to all folders and can write/read completly. I won't this anymore, so I created a new sql user with access to the specific database only.

    My problem is now I don't know how to grant access to the user to the file system folder (e.g. D:/public-folder). Has the user to be in a specific group to read/write from this folder? Have I add a group/user to the Security Tab in the folder properties?


    Another question is, in which group is the user "sa"? Why can he have complete access to all hard drives of the server? I think that's a too much just for one sql user.

     

    Greets,

    Burak

    Thursday, December 15, 2011 7:41 PM

Answers

  • What I don't figure is how I can give a SQL user permission to use the service account to access the file system? If all access is going through the service account I still have to say which user can do that and which don't, haven't I?

    The tricky part is that we need to know what means of access that the application uses. From what you say in your post, it sounds like a browse dialogue, which I would assume uses the same thing as SSMS.

    I profile SSMS, and it seems to use a couple of extended stored procedure, but these procedures are not documented in Books Online. Therefore I cannot say what permissions that are required. But it is not unlikely that you need membership in some heavy-duty server role for these.

    But this is still speculation on my part, since I don't know what access we are talking about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Wednesday, December 28, 2011 1:45 AM
    • Marked as answer by Peja Tao Monday, January 2, 2012 7:13 AM
    Monday, December 19, 2011 10:58 PM

All replies

  • Are you accessing the file system folders? Which mechanism do you use for that?

    Normally, you don't use SQL Server to access files.

    How files are accessed in Windows depends on the command. In some cases, SQL Server impersonates the actual user when you log in with integrated security. In other cases, access is always made by the service account for SQL Server. The service account is also used when you are logged with SQL authentication. "sa" is such a case.

    Note that no SQL login has permissions of its own in the file system, as that is outside the realm of SQL Server. But if the service account has access, an SQL login has access.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 15, 2011 10:29 PM
  • Hello,

    thanks for your answer.

    I don't really know how the access on the file system folder are made. I think I have to ask the IT support of the company of the program.

    So, if I logged in with a normal sql account and want to access on hard drive E:/, I'll get permission by the service account ? What is the reason that one sql user gets permission by the service account? I think not every use have access to file system around the sql server.

    Friday, December 16, 2011 7:39 AM
  • To answer your question, we need to do what the application is doing. At this point we don't even know if the application access the disk through SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 16, 2011 1:27 PM
  • Yeah, I thought that I have to resolve this first with the developers. But I think it access the disk by SQL Server, because I tried the same action with an other user as "sa" and it doesn't work.

    Thank you for your help!

    Friday, December 16, 2011 2:28 PM
  • Erland is right..

    Simple logic when you connect using SQL authentication then it will use the service account credential to access the file system, if you connect using windows authentication then it will use the windows user credentials to access the file system.

    All SQL logins are only scoped with in SQL Server so you can't access anything outside SQL server using SQL authentication.

    As Erland questioned, can you confirm what exactly you are trying to do in filesystem level?


    Vidhya Sagar. Mark as Answer if it helps!
    Monday, December 19, 2011 12:11 PM
  • Hello,

    thank you for your Answer. I didn't have time to talk with the technical support of the company of the program. So I will mention what it should do:

    I have to login via an SQL/Windows Authentification to the database of the program. The administration tool has the function to backup and recovery a database. Sadly the program is always trying to access the file system of the server where the SQL Database is running! And I think that the permission to access it comes within the authentification to the database.


    If I login with the sql uer "sa" I can lookup every directory/file of the file system where the SQL Server is running. If I login with a new sql user I see nothing on the server.


    Like I said, I think it's more a problem of the program. So I have talk with them first.

    What I don't figure is how I can give a SQL user permission to use the service account to access the file system? If all access is going through the service account I still have to say which user can do that and which don't, haven't I?

     

    I have to excuse for my english language. I'm from germany and not really good ;).

    Monday, December 19, 2011 6:46 PM
  • What I don't figure is how I can give a SQL user permission to use the service account to access the file system? If all access is going through the service account I still have to say which user can do that and which don't, haven't I?

    The tricky part is that we need to know what means of access that the application uses. From what you say in your post, it sounds like a browse dialogue, which I would assume uses the same thing as SSMS.

    I profile SSMS, and it seems to use a couple of extended stored procedure, but these procedures are not documented in Books Online. Therefore I cannot say what permissions that are required. But it is not unlikely that you need membership in some heavy-duty server role for these.

    But this is still speculation on my part, since I don't know what access we are talking about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Wednesday, December 28, 2011 1:45 AM
    • Marked as answer by Peja Tao Monday, January 2, 2012 7:13 AM
    Monday, December 19, 2011 10:58 PM