Post reply

How execute sp_OACreate ... without be sysadmin role member

  • May 13, 2010 at 7:41 am

    #386926

    Hi all,

    I have a user (naming 'MYUSER')login that is executing an sp. It gets the follwoing error:

    Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1

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

    Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1

    EXECUTE permission denied on object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.

    Msg 229, Level 14, State 5, Procedure sp_OADestroy, Line 1

    EXECUTE permission denied on object 'sp_OADestroy', database 'mssqlsystemresource', schema 'sys'.

    Everything i can find on the net say that to execute 'MYUSER' user the SP he have to be a member of sysadmin SQL Server role.

    Somebody know if exist any other option? How i grant to 'MYUSER' execute privilegies to 'sp_OACreate','sp_OAMethod' or 'sp_OADestroy' sp-s (without doing 'MYUSER' SQL Server admin?

    Many thanks in advance.

  • May 13, 2010 at 7:48 am

    #1166872

    i think you could put the code in a stored procedure and use EXECUTE AS.

    as an admin, creat ea procedure with the work inside it like this:

    CREATE procedure pr_CallExternalProcess

    WITH EXECUTE AS 'dbo'

    AS

    BEGIN

    --your sp_OACREATE stuff goes here

    END

    then you cna simply grant your user EXECUTE permissions for the proc. they can use it, but do not get granted any extra priviledges.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 13, 2010 at 8:02 am

    #1166886

    Hi Lowell,

    I already test the solution that you propose to me.

    I receive the next error message after executing 'pr_CallExternalProcess' (after i execute it with one admin user or with 'MYUSER' user):

    " Error Message User does not have permission to perform this action.

    Error Severity 16

    @ErrorState 1

    "

    Any other idea?

    Many thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply