locked
Execute permission denied on with the grant and with grant permission enabled. HELP PLEASE. Granting excute doesnt work. RRS feed

  • Question

  • Here is the code that Im running.  The user Tester has the both Grant and With Grant checked.

    IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AAAtestOW')
    
    BEGIN
    
    DROP Procedure dbo.AAAtestOW
    
    END
    
    GO
    
    CREATE Procedure dbo.AAAtestOW
    
    AS
    
    select
    
    'aaa'
    
    GO
    
    
    

    If i add the grant execute command for me I get
    "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."

    If I try
    GRANT EXEC ON dbo.AAAtestOW TO [public]
    I get the following error
    Cannot find the object 'AAAtestOW', because it does not exist or you do not have permission.

     

     

    I want to have the database locked down so I dont want to grant the db_securityadmin. 

    What do I have to do????????

    Tuesday, October 5, 2010 4:19 PM

Answers

  • Have a user create a stored procedure where he has only ddladmin, dbreader.
    After create the stored procedure be able to execute it.
    The thing is have the security very tight.  So im  my case i have a to grant an execute somehow. 
    Think I found the solution GRANT execute on the schema itself.

    • Marked as answer by OrsonWeston Tuesday, May 24, 2011 5:01 PM
    Wednesday, October 6, 2010 7:33 PM

All replies

  • Works fine for me, what are you trying to do? This is what I did...

    1) Created Server Login Test.

    2) Created Database user Test, from Server Login Test.

    3) Create a Test Proc.

    4) Granted Database user Test, EXECUTE WITH GRANT permissions.

    5) Create another Server Login Test2.

    6) Added Test2 Server Login as Database User Test2.

    7) Login as Test.

    8) Executed "Grant Execute on dbo.Test to Test2" -- No Issues.

    9) Executed "Grant Execute on dbo.Test to [public]" -- No Issues.

    What is your goal?  Maybe another issue or another method can be employed.


    Thanks. Mohit. -------------------------------------- MCTS: SQL Server 2005, MCITP: Database Administrator. http://sqllearnings.blogspot.com/
    Tuesday, October 5, 2010 4:47 PM
  • Have a user create a stored procedure where he has only ddladmin, dbreader.
    After create the stored procedure be able to execute it.
    The thing is have the security very tight.  So im  my case i have a to grant an execute somehow. 
    Think I found the solution GRANT execute on the schema itself.

    • Marked as answer by OrsonWeston Tuesday, May 24, 2011 5:01 PM
    Wednesday, October 6, 2010 7:33 PM