SQL Server 2005 Permissions and ASP.NET

I have been trying to test some stored procedures to make sure their error recovery is coded properly. One thing I wanted to try was to deny delete permission on a particular table so I could check out a delete sp that requires multiple table deletes. I denied permission for the user specified in my connection stringon that table. Now when I tried a delete from sqlcmd I get a permission denied error. However, when I do the same operation from my web page it works swimingly. Can't figure out why? Is it because granting EXEC privileges gives super powers?

 

What is the sql authentication you are using on your web application. Most of our customers use there default db login for the connections string. The default dp login by default will have dbo privilages. Try checking your connection string for your web application again. You might be using the default db login.

 

I created a new user using the tool online. That isthe userI use in my connection string.


I think I just proved to myself through some tests that granting EXEC gives mighty powers. This was my test:


1. Create a new row in table 'foo'


2. Deny delete access on table 'foo' to my user in my connection string


then from sqlcmd:


3. delete foo where id=N


this fails with a delete permission denied error


4. now call a stored procedure that does the same delete that my user has EXEC privileges on


delete is successful


Maybe this is the way it is supposed to work, but I did not expect it.


tmr