Can't run views and stored procedures

[John Dohn]

I can work with them just fine in VS but when I run my app, I get a select
permission denied error on any view and execute permission denied on any SP
in my web app.

All was worked fine until Friday and not since then.

Win Server 2003 SP4
VS 2003
ASP.NET 1.1
IIS 6.0

I am developing on the server machine and am logged in with admin
privileges.
My account is in the VS Developers group.

Looking for the best way to troubleshoot this.

Much obliged.

 

[David Wier]

If you've got Windows authentication on your connectionstring to SQL Server,
you must grant execute permissions on sProcs to the same user named in the
connectionstring

David Wier
http://aspnet101.com
http://iWritePro.com - One click PDF, convert .doc/.rtf/.txt to HTML with no
bloated markup

 

[John Dohn]

Thanks. I am using Windows Authentication.

Do I need to manually set the permissions even if the view and stored proc
were created in VS 2003 Server Explorer (right click/ New)?

I am logged in as admin on the development machine which is running Win
Server 2003, SQL 2000, IIS 6, ASP.net 1.1 and VS 2003. I am a member of VS
developers group.

By the way, just as a sanity check, the same view and stored proc worked
just fine when I loaded VS with a windows forms app that contains the same
connection string (except a Modifiers property set to Friend whereas web
form conn str set to Protected - all by VS not me). The win form has the
same code behind as the web form. So it seems like the permissions are right
for windows but somehow not right for web.

 

[David Wier]

Since you are connecting with a particular user - when the database tries to
run a stored procedure, it tries to run it AS that user.
Therefore, the stored Procedure must grant execution permissions to that
user.

David Wier
http://aspnet101.com
http://iWritePro.com - One click PDF, convert .doc/.rtf/.txt to HTML with no
bloated markup

 

[John Dohn]

Thanks, Dave. I must be a tad dense here and no doubt missing something
really obvious but I'm starting to catch on. So I can just go into SQL
Server and set the execute and select permissions for the user account I am
logged in as on the W2K3 machine and then that should solve my problem.
Actually, I tried that and wasn't able to figure out how to add my account,
but I did see that Guest and Public accounts were already (I assume by
default) set up, but had no permissions on the SP. So I set them both to
execute and voila! all is working.

So I guess my questions now are, doesn't VS and ADO.net handle this when you
create views and sprocs within VS (it seemed to for me until a few days
ago)? If it doesn't set permissions automatically is there a way to do it
thru the VS Server Explorer gui?

Also, when the app is in a production environment (let's say in a shared
hosting account), and unauthenticated Internet users are accessing the page,
is the user account at that point going to be ASPNET? Would I use
application role permissions?

....Still a bit confused after all this time.

Thanks.

John