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'johndoe@example.com',
		@RecipientName = N'Jane',
		@RecipientAddress = N'janedoe@example.com',
		@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!

Recommended Answers

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]
Jump to Post

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 …

Jump to Post

All 5 Replies

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]

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!

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.

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.

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.