1

I have a classic ASP application running under IIS (v8.5.9600) on Windows Server 2012R2. One of the pages makes use of a file upload control that allows a user to upload a data file to be processed by the system. Once the file is uploaded a stored procedure is executed that takes the name of the file uploaded and inserts it into a table.

Some of my users have been receiving an error message after uploading the file. Checking the IIS log I see the error is the following ...

244|80040e09|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]The_EXECUTE_permission_was_denied_on_the_object_'myStoredProcedureName'__database_'myDatabaseName'__schema_'dbo'.,

From reading other articles it seems as though I need to grant execute permissions to this specific stored procedure. My question is this ..

  1. Which user needs the execute permissions? Is it the user that the database connection specifies or is it the user that the website runs under via IIS?
  2. This error does not occur with most users. It only seems to be occurring with a few of the users that upload files. If the issue were permissions shouldn't it be occurring with all users?
Community
  • 1
  • 1
webworm
  • 10,587
  • 33
  • 120
  • 217
  • Execute rights requires for the user there within the config. Might be one of the load balance server is having different user name. When client hits that specific server they may be getting this error. – Bhasyakarulu Kottakota Sep 18 '14 at 14:44
  • I keep trying to reproduce the error myself but I am unable to. I have tested inside the network and outside the network. I have tried different browser/OS combinations but I cannot reproduce the error. – webworm Sep 18 '14 at 14:49
  • webworm: The execute permission needs to be given to the user in the connection string. @BhasyakaruluKottakota this is [tag:asp-classic], not [tag:asp.net]. – Paul Sep 18 '14 at 15:23
  • If a stored procedure needs to insert data into a different database other than the one where it is located, does the user executing the stored procedure need insert permissions on the different database? – webworm Sep 18 '14 at 15:31
  • @Paul .. if you make your comment into an answer I will accept it. – webworm Sep 19 '14 at 13:41

1 Answers1

2

webworm - many thanks.

The execute permission needs to be given to the user in the connection string.

The user manipulating the database is the one that is mentioned in the connection string. It is possible to use multiple connection strings, of course, for different parts on the application, linking to many different databases, though this can become a nightmare to manage. I recommend creating all of the connection strings in the global.asa and limiting the SQL user accounts to as few as possible. It's easier to give the same account access to multiple databases on the same server...

Paul
  • 4,160
  • 3
  • 30
  • 56