SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Stored Procedure failure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 02/20/2014 :  08:17:22  Show Profile  Reply with Quote
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

USA
36997 Posts

Posted - 02/20/2014 :  12:40:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
10 Posts

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

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 02/21/2014 :  12:17:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
10 Posts

Posted - 02/24/2014 :  05:34:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000