Yesterday when i was deploying the application on the server in London.  I was encountered with the problem while i was trying to access the login page of application. After getting the user name and password when application tries to connect the SQL server 2005 database, it thorws out this exception “The EXECUTE permission was denied on the object ’stored procedure name’, database ‘database name’ , schema ‘username’“. I search it on the web and got the solution and here it goes.

STEP 1: Open SQL Server 2005 through SQL Server Management Studio Express.

STEP 2: Right Click on the database to which you want to connect.

SQL Server Database Properties Screen Shoot - nDoubleOr.com

STEP 3: A properties pop up will open. Click on permissions on left hand side.

SQL Server Database Properties Popup Screen Shot- nDoubleOr.com

STEP 4: On the right hand side below the database name you will see the while box users or roles.  If there is no item inside this box click ‘Add’ button. A pop up will appear as shown below. Click ‘Browse’ button.

SQL Server Database Select User OR Roles Popup Screen Shot- nDoubleOr.com

STEP 5: ‘Browse for Objects’ pop up window will appear and you will see ‘guest’ and ‘public’ in the list with check boxes.

SQL Server Database Browse for Objects Screen Shot- nDoubleOr.com

STEP 6: Select the ‘guest’  and click ‘OK’.

STEP 7: ‘Guest’ will be appeared in the list  & permission list will be appeared on the screen.

SQL Server Database Properties Popup 2 Screen Shot- nDoubleOr.com

as shown below.

STEP 8: You ‘ll see columns of ‘Permission’, ‘Granter’, ‘Grant’,  ’With Grant’ and deny.  Scroll the list and select the ‘Grant’ check boxes against permissions.  Usually Connect, Execute, Update, Insert, Delete, Create View permission are given to the object but you can define anything as you wish.

Is this post helpful for you or any suggestions ? Your comments will be highly appreciated.