Solved

How do I grant execute permission on sp_oacreate in the master db?

Posted on 2007-03-29
6
22,853 Views
Last Modified: 2012-06-27
Hi everyone
I am trying to run the sp_oacreate master procedure from within a trigger on a sql 2005 database table. So far the only solution i have found that works is to add my database role and user roles just as they are in my current database to the master database as well. I am using NT authentication.

The error I'm getting when i update the table with the trigger in vb is:
42000: [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Any help would be appreciated
Effie
0
Comment
Question by:tiseffies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 18815391
For you to execute the sp_oacreate stored procedures you need to be in the system admin group, or you can go into master database and give your self exec permissions on each procedure in extend stored procedures section.

use master
go

grant exec on sp_OACreate to yourUserLogin
GO

0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18816072
According to Books on Line and y persona experience sirees is correct that you ahve to be a sysamin in order to rus sp_oacreate. However, I do not believe you can grant execute on that procedure to other users that are not members of the sysadmin role.
0
 

Author Comment

by:tiseffies
ID: 18822280
Yes I know of the sysadmin mentioned. Though I'm not sure how to do that in sql2005.
The other thing is this would be rejected by my client because it would pose a security issue... messing around with the master database. So i was hoping to use another method.. Unfortunately i'm stuck for ideas.

thanks for you reply
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 6

Accepted Solution

by:
DantheDBA earned 500 total points
ID: 19285183
Using the sysadmin fixed server role will allow access to these SPs, but is generally a bad idea due to the security risks. You may consider an alternate approach such as a certificate.

In any case to add a login to the server level sysadmin role execute the following:

use master
exec sp_addsrvrolemember 'loginname', 'sysadmin'

Contrary to rboyd56's statement, it is possible (I have done it successfully myself)  to grant permissions on these system stored procedures to a user that is not a member of the sysadmin role. Run the following to grant the group of system stored procedures to a specific username

use master
grant exec on sp_OACreate to username
grant exec on sp_OAGetErrorInfo to username
grant exec on sp_OAMethod to username
grant exec on sp_OAGetProperty to username
grant exec on sp_OADestroy to username

You will want to map the existing login for your username to a user in Master because the permission cannot be directly addressed to the login.

Thanks,
Dan
0
 

Author Comment

by:tiseffies
ID: 19288548
To All

I have completed this task finally, I had to give guest permission to the above sp's, apparently mandatory when referencing a second database with an Application role connection on the first.
0
 
LVL 3

Expert Comment

by:kworks
ID: 25824326
@tiseffies:

Since I'm having similar problem, I'm just wondering about the sentence "give guest permission to the above sp's". What would that be, precisely?

And, if that is the case, what is the difference between it and the accepted solution??

Your help is much appreciated.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question