EXECUTE permission denied on object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

EXECUTE permission denied on object

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2008-02-12 : 15:17:57

I know this is a cross-post, but I really need some help and am not getting it on the other forum - this was probably the place to post it anyway.

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!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 16:33:23

quote:
Can someone please explain exactly how to grant the permission

open a query window (Query analyzer - sql 2000 OR Management Studio sql 2005) as DBO or SA

run:
GRANT EXEC ON <stored proc> TO <user>

Test by opening another window as <user>. (this should be your UID from your connection string)
execute the SP directly in the query window.

If that works then try again from your application. If you are having problems at that point at least you know it is not in the database side. Pay attention to the error message, it should say specifically which object and which user.

Be One with the Optimizer
TG

hey001us
Posting Yak Master

185 Posts

Posted - 2008-02-12 : 16:35:27

Here the steps may help you:
1. put your credential at web.config file as below
<add key="SqlConnect" value="SERVER=***\***;Initial
Catalog=*****;UID=user;PWD=pass;" />

2. Give execute permission for “user”
Go to SQL manager >> Database >> Stored Procedures >> right click to SP select Properties >> Click Permission Button and Click check under EXEC.

3. Application server and database server both are running in same box? If no you need to check your firewall rules

Try below commands at command prompts:
telnet <ip> <portno>
port no for SQL 1433 default and http: 80

hey

ann
Posting Yak Master

220 Posts

Posted - 2008-02-12 : 16:51:51

When I try to use the "Grant Exec On" I get
"Cannot find the user '***', because it does not exist or you do not have permission."

I can see the user in the db under the users, so it does exist and I have admin rights, so that's not it.

I do not think at this point I have the user setup correctly.

Can you explain how to correctly create the user - thanks

hey001us
Posting Yak Master

185 Posts

Posted - 2008-02-12 : 17:01:56

is that user got access to the db?

hey

ann
Posting Yak Master

220 Posts

Posted - 2008-02-12 : 17:06:52

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.

I'm not sure if they have access to the db - I assumed so since I mapped the user to the db - there is probably a step I am missing after adding them to the login....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 17:18:38

quote:
Originally posted by ann

When I try to use the "Grant Exec On" I get
"Cannot find the user '***', because it does not exist or you do not have permission."

I can see the user in the db under the users, so it does exist and I have admin rights, so that's not it.

I do not think at this point I have the user setup correctly.

Can you explain how to correctly create the user - thanks

Was this database restored from a different server? If so, the (db)user may have lost association with the (server) login.

Be One with the Optimizer
TG

ann
Posting Yak Master

220 Posts

Posted - 2008-02-12 : 17:27:53

No, it wasn't restored.

I am sure that I do not have the user set up correctly.

At this point, I would like some information on how one goes about setting up a user correctly so that I can:

a. connect to the db.
b. execute a sp

Surely this should be something that most developers do - I do this all the time in sql 2000, why I can't manage this in 2005 - I just don't know :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 18:06:21

This is how I do it. I just ran this code successfully:


--Create the Login
use master
exec sp_addLogin @loginame = 'Junk' 
	,@passwd = 'JunkPassword'
	,@defdb = 'master' 


go
--create a test SP
create proc junk_usp as select getdate()
go
--Add the user to the database
exec sp_adduser @loginame = 'Junk' 
   	,@name_in_db = 'Junk' 

--grant exec
grant exec on junk_usp to Junk
go

/*
CONNECT AS JUNK and Run the SP.

Close the window that Junk is logged into
*/

--clean up
exec sp_dropuser Junk
exec master..sp_droplogin Junk

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources