SQL Server security question. RRS feed

  • Question

  • I have a stored procedure which is run by the application. In the application I specify the path to a folder which has couple of files that get uploaded to the server.
    The stored procedure will be given the path to the files and the stored procedure will then read the folder and upload the files to the database. The stored procedure uses the BULK INSERT statement to load the data in the files to the SQL Server. The BULK Insert statement uses a format file which has the format definition. The format file is in the same folder. The format file is created by the application

    So basically, the application needs read/write to  the folder and SQL server needs only read security to the folder.
    So if I want to set this security up how to do I do it? What users in SQL server should be given permissions to  have read/write access to the folder ?

    Tuesday, October 20, 2009 3:21 AM


  • Ragha,

    This depends on whether the application uses Windows Authentication or SQL Logins. If it's using SQL Logins, the security context is made as the SQL Server process, and thus the account running SQL Server would need to have access to the directory/files. However if the connection is made by Windows Authentication, the user's windows account needs to have access to the directory/files and the SQL Server process account won't be used.

    You can read more about it here.

    hope this helps,
    Tuesday, October 20, 2009 12:37 PM