none
Dropping and Recreating an Stored procedure lost access

    Question

  • Hi,

    Recently after an application up gradation there were some object changes to our Tables, this needed developers to amend some stored procedures ,these procedures have long been in the database for more than 3+ years, this needed some procedures to be backed up and then altered.  In one case we had to restore the amended procedure since the amended was not working, since 2 proc cannot be of the same name we had to drop the one that was there for a long time and created it with the new one that we backed up. 

    Problem was when application was making its connection it clears sent out an error for them as below

    The EXECUTE permission was denied on the object 'ABCD', database 'XYZ', schema 'dbo'.

    Application users are not certain or confident on the user name and so right clicking the procedure and checking properties (permissions) we dont see any access provided to the proc to any SQL user ID, we checked the same on the backed up procedure as well which we just altered with procedurename_backup there as well there were no permission provided .

    When there is no access provided to the proc to any user explicitly then why do we face this issue , is it because we dropped and recreated the procedure. Finally running below query solved the issue, but granting the proc with public access would mean an security issue to my understanding to be answered with audit.

    GRANT EXEC ON dbo.xxxxxxx TO PUBLIC

    Can someone help me know how to decipher this issue where the mistake happened and how to find the user that really needs the execution rights to come out of this Public access.

    Since i used to take a backup of the security properties of the procedure, i had below details of the procedure that was dropped and recreated 

    Schema Owner Login - sa
    Schema Owner -dbo
    Sechema ownerPrincipal ID -1
    Schema owner- 0*01
    Schema Owner Type - SQL_USER
    Schema ID - 1
    Schema name - dbo
    Object type- SQL_STORED_PROCEDURE

    Regards

    Eben

    Tuesday, June 05, 2018 8:15 AM

All replies

  • is it because we dropped and recreated the procedure.

    Hello Eben,

    Yes, when you drop an object like a stored procedure then also all permissions are droped, too and after re-creating the object you have to grant all permissions again.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 05, 2018 8:49 AM
    Moderator
  • Hi Olaf, Thanks for the answer, here my situation is i did backed up the procedure by create script with procedurename_backup , but when i check the permission of the backed up procedure i.e properties--> Permissions , i can see the owner of the proc to be "dbo" but under users or roles i find nothing added .. this is how it had been from the beginning.( i still have the backed up procedure with me on the DB)

    But after dropping and recreating i got the posted error and so i had to grant EXEC to public , now under users or roles on the properties --> permissions i explicitly see Public added with EXEC rights. 

    My situation is how it worked earlier without explicitly added and when i backed up the proc how the rights got missed out. Is there someway to find it out.

    Regards

    Eben

    Tuesday, June 05, 2018 10:32 AM
  • It is not clear to me: is permission denied on the stored procedure itself or an object accessed by the procedure?

    You may have to restore a backup of the database to see how permissions were granted. When you script a procedure, permissions are not included by default. Then again, you could inspect the permissions for other procedures in the system to get some guideance.

    I also like to add that things like this, that is to which users or groups permissions is normally granted is something you should have documented, as this is something you often need to know for new stored procedures.

    Tuesday, June 05, 2018 11:10 AM
  • Thank you
    Tuesday, June 12, 2018 6:59 PM
  • i did backed up the procedure by create script with procedurename_backup


    But your script didn't include the permissions as a GRANT Statement, it's only the script of the SP.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 13, 2018 6:41 AM
    Moderator
  • Hi Eben,

    Did you resolve your issue? If so, please kindly mark the corresponding reply. It'll benefit others with relevant issue.

    If not, please feel free to ask.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 18, 2018 7:26 AM