Grant to PUBLIC / OLE Automation

  • I enabled OLE Automation using ==> http://msdn.microsoft.com/en-us/library/ms191188.aspx

    But when a user is trying to access it it is giving the error

    Msg 229, Level 14, State 5, Line 1

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

    So i tried granting the privilege from master

    GRANT EXECUTE ON [sys].[sp_OACreate] TO PUBLIC;

    That complains the user 'MyUser' does not exist. In other words it expects the user to exist in the master database.

    http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22777078.html

    But if i will create the user in master then, the grant will go to the user in the master database. Not my regular

    database user.

    For now i have granted access to PUBLIC to get around this.

    1. How do i grant access to a private user?

    2. Is it safe to grant to public the OLE automation stuff? What are the risks involved?

    Thanks

  • I think you have to restart the server after turning it on.

    Alex S
  • Granting permission to public for these sprocs is a potentially huge security problem just waiting to happen. The sp_OA sprocs allow a user to call the methods of the applications installed on the server. This could easily be exploited. This is the reason that they are disabled at installation time.

    If there is a need to allow these sprocs to be used, only grant permission to those individuals who need them - NOT to public.

  • Hello Happycat

    Thanks for the information. But the problem is i am not able to grant the privilege to that particular user. Are you aware of any work arounds?

    Thanks

  • Has the login been granted access to master ? This is separate from creating the login

  • I don't understand this. How do i grant access to master? Does it mean granting access to views under master? If so what views? All views? Sorry, if my question sounds trivial.

    Thanks

  • Once you have created the 'MYUSER' login, you then need to create that user in the database. Through the GUI, this can be accomplished by double-clicking your user under security and then going to User Mapping and putting a check in the databases for which the user will have access (master in this case).

    Or VIA TSQL

    Use Master

    Go

    CREATE LOGIN MyUser DEFAULT_DATABASE=[Master] --This is the part you have already done

    CREATE USER MyUser FOR LOGIN MyUser --this needs to be done

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

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