Stored Procedure - Can CREATE but not EXECUTE

[SQL_n00b]

The title to the thread pretty much describes what I am getting stuck at. I was able to create a procedure successfully, but everytime I try to execute it, I get an error:

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Logically speaking, if the DB admin has granted me permission to create SPs, wouldn't he have granted me permission to execute them too? Below is how I am calling the SP:

USE [myDBName]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_SMTPMail]
		@SenderName = N'John',
		@SenderAddress = N'[e-mail]',
		@RecipientName = N'Jane',
		@RecipientAddress = N'[e-mail]',
		@Subject = N'Test SP Execution',
		@Body = N'Testing 1-2-3',
		@MailServer = N'smtp.example.com'

SELECT	'Return Value' = @return_value

GO

(Of course, all the values above - including the MailServer attribute - are fake)

How do I get around this? Am using MS SQL Server 2005

Thanks!

[sknake]

By default normal users do not have execute rights on newly created sprocs. Here is how you would grant execute rights on a sproc called "LockRow" to "DOMAIN USERS":

GRANT EXECUTE ON dbo.LockRow To [DOMAIN USERS]

[SQL_n00b]

By default normal users do not have execute rights on newly created sprocs. Here is how you would grant execute rights on a sproc called "LockRow" to "DOMAIN USERS":

GRANT EXECUTE ON dbo.LockRow To [DOMAIN USERS]

Thanks Scott, I tried using your code snippet and it seemed to have run successfully too. But after that runs, if I go back to executing the sp_SMTPMail SP, I still get the error. I checked the permission in the procedure's Properties dialog and it looks like I do have permission to EXECUTE it. What am I missing?

Another question: Why does the error message list the 'mssqlsystemresource' DB. I have created the Sp in MY DB, not one of the system DBs!

[sknake]

Well the code snippet I gave you was an example and will only grant you effective execute permissions if you are in fact a domain user ;) If not you should use grant access to your [LOGIN NAME]. Post the T-SQL query where you are executing the sproc.

You should be executing it like "exec dbo.sp_SMTPMail" so it does not infer a schema. If you don't have the dbo. prefix try adding it and see if it clears up the error.

[Ramesh S]

Try this.

GRANT EXECUTE  ON sp_OACreate to UserLogin
GO

Here UserLogin is the name of the login that you use to connect with database.

[SQL_n00b]

scott/ramesh,
I am in fact a domain user and the code snippets above seemed to run successfully. But it turns out that the IT dept does not want me to execute SPs like Sp_OACreate because they seem to be security risks from a server point of view; therefore Users are not granted the right to execute them.
I have been instead advised to use the server-side language to trigger emails on DB update (which is what the aim of the SP was).

thanks anyway, for your help!