locked
Permissions RRS feed

  • Question

  • User1979860870 posted

    Hi

      How to grand permission to execute sp_OAmethod  to all users

    Thanks

    Sunday, November 8, 2020 4:52 PM

All replies

  • User-1330468790 posted

    Hi jagjit saini,

     

    I understand that your question is how to make a specific stored procedure executable by any other users.

    Consider use of the EXECUTE AS capability which enables impersonation of another user to validate permissions that are required to execute the code WITHOUT having to grant all of the necessary rights to all of the underlying objects (e.g. tables). EXECUTE AS can be added to stored procedures, functions, triggers, etc.

    Add to the code as follows right within the Stored Procedure:

    CREATE PROCEDURE dbo.MyProcedure WITH EXECUTE AS OWNER

    The way SQL Server already works, is that in most cases you only need to grant execute rights to a stored procedure and rights are granted to all objects that are referenced within the stored procedure, so you do not need to give implicit rights to either update data or call additional stored procedures.  This is handled via ownership chaining.

      

    Related link: 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188676(v=sql.105)?redirectedfrom=MSDN

     

    Hope helps.

    Best regards,

    Sean

    Monday, November 9, 2020 3:09 AM
  • User1979860870 posted

    Hi

      When i write like below

    ALTER PROCEDURE [dbo].[Inv1]
    WITH EXECUTE AS OWNER 

    @filepath VARCHAR(100)

    it says "Incorrect Syntax near filepath"

    Thanks

    Monday, November 9, 2020 4:24 AM
  • User-1330468790 posted

    Hi jagjit saini, 

      

    I think the error occurs because of a syntax error.

    As you can see from the official document: ALTER PROCEDURE (Transact-SQL): Syntax

    -- Syntax for SQL Server CLR Stored Procedure  
      
    ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
        [ { @parameter [ type_schema_name. ] data_type }   
            [ = default ] [ OUT | OUTPUT ] [READONLY]  
        ] [ ,...n ]   
    [ WITH EXECUTE AS Clause ]  
    AS { EXTERNAL NAME assembly_name.class_name.method_name }  
    [;]  

    The expression part [WITH EXECUTE AS Clause] should be put in the end of the Transact-SQL statement.

      

    Hope helps.

    Best regards,

    Sean

    Monday, November 9, 2020 9:44 AM
  • User-1330468790 posted

    Hi jagjit saini,

     

    Any update?

    If you still have problem related to this topic, feel free to let me know.

     

    Best regards,

    Sean

    Wednesday, November 18, 2020 8:22 AM