Connecting to Sql Server 2005

[ann]

I am trying to get my application (.net 1.1, c#) to run some sp's (sql server
2005). I can finally connect to my db, but when I get to the part that
executes the sp's I get the infamous EXECUTE permission denied on object blah
blah blah....!

When I check the permissions on the stored procedure, I have granted execute
on the sp for the user - so I don't understand why I can't execute it!

My connection string:

<add key="SqlConnect" value="SERVER=***\***;Initial
Catalog=*****;UID=user;PWD=pass;" />

I am using sql server authentication - I created a SQL server user in the
logins, mapped it to the database, grant permission to connect to db, login
enabled.

from the database, for each sp, the user has execute.

Sorry if I sound frustrated, it's because I am.

Can someone please explain exactly how to grant the permission - I am
probably missing something - but when the user is granted permission to
execute a sp, that should be enough..no?

So, all I want to do is create a user that can connect to the db and execute
sp's - I want to be able to pass these credentials via my application - so if
anyone can tell me how to create the user and how I can have them execute the
sp and use those credentials the way I described - I would very much
appreciate it.

Any help would be soo appreciated - I am so tired of this!

 

[Scott M.]

What "user" have you given permission to? If this is an ASP .NET
application, it needs to be the ASPNET user account that has permission to
execute your SP's, not the actual user (person) that is using your
application.

 

[Cowboy \(Gregory A. Beamer\)]

When you create the logon (or alter the login) in Enterprise Manager
(Express Manager for SQL Express) go to the User Mappings tab and gie the
user NOT ONLY access to the database in question, but give it a role that
allows it the permissions it will need. db_owner gives all permissions, but
also allows hackers who compromise your web.config free reign. But, you
probably need at least reader and writer permissions. Or, you can write a
stored procedure "layer" to shield the underlying tables and grant
individual rights to the sprocs in question.

There are ways to set up for Windows Authentication, if that is possible
(not usually with an ISP, unless they set up the user as part of your
account set up).

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************