Execute Permission denied when running from IIS

My development environment is IIS 5.1, asp.net 2.0, Visual Web Developer 05 Express, MS Sql 2005 Express with XP Pro. I used a "stored procedure" in a webpage Formview to insert a record in a child table after inserting a record in the parent table. All went well when testing in VWD.

After deploying to remote site on same machine, I get an error

"EXECUTE permission denied on object 'usp_Insertdataset', database 'Job_Tracker_SQL', schema 'dbo'"

when trying to insert. I know that SQL Express is not suppose to support stored procedures. Is there a work around? I need to host this site on this machine for the immediate future.

Thanks

cbrcdr

I think that you are incorrect in stating that SQL Server Express does not support stored procedures. I'd be curious to see where that is documented (but, I have been proven wrong in the past!).

But, in any case, I would bet that in your development environment, you were connecting to the database as a DBO (database owner), and the same was not true for the remote site. By default, no one but a DBO is granted any permissions on database objects.

You should be able to grant execute permissions to the "Public" role, which should take care of allowing anyone who is permitted to connect to the database to execute your stored procedure. As an alternative, you can grant execute permission to invididual users too.

Execute the following SQL while connected to your database as the DBO user (i.e., from a query window or whatever mechanism you have available):

GRANT EXECUTE ON usp_InsertdatasetTO Public

|||

Jason

I saw a Feature Matrix on a Mircosoft Webpage that compared all the SQL products and it indicted that SQL Express did not support Stored Procedures. Maybe it was not up to date. I assumed it was right but you are. I used SSMSE and set the permissions on the Stored Procedure to execute for the login and it works.

Thanks, that was my last big hurdle for this phase of my application.

George