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
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Stored Procedure failure

Author  Topic 

AfcDons
Starting Member

10 Posts

Posted - 2014-02-20 : 08:17:22
Hi,

I have a stored Procedure that is failing with the following errors,

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1
The EXECUTE permission was denied on the object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.


Looking around I can see that I can give permissions via the code below,

Use master
go

grant exec on sp_OACreate to sa
GO


When I run this I get the following failure message

Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'sa', because it does not exist or you do not have permission


This stored procedure is run within an application which uses the sa login to connect to the database. Is it failing because I can't give user details to the SA user. Is there a way to make this work?

Thanks for any advice.
John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-20 : 12:40:12
Isn't sa granted sysadmin server role?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AfcDons
Starting Member

10 Posts

Posted - 2014-02-21 : 04:11:18
Thanks for the reply.Yes it is set to sysadmin.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-02-21 : 12:17:20
Try granting it to dbo.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AfcDons
Starting Member

10 Posts

Posted - 2014-02-24 : 05:34:43
Hi,

I tried that and got the following,

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

thanks
John
Go to Top of Page
   

- Advertisement -