Post reply

modify Store Procedure

  • August 11, 2008 at 10:06 am

    #130634

    Hi,

    I have a SQL 2005, i have a windows login and this login can´t modify SP, this login have the following permissions

    Server Role:

    Public

    User Mapping:

    DB1

    db_datareader

    db_denydatawriter

    public

    My question is why this login can´t modify the SP in DB1

    Thanks for you answers

  • August 11, 2008 at 10:29 am

    #856224

    Hello,

    These Role memberships do not include the rights to modify DB objects such as SPs.

    Membership of the db_ddladmin or db_owner roles would grant sufficient rights, or you can grant the Alter permission for specific SPs to the user.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • August 11, 2008 at 10:33 am

    #856227

    ok,

    but i can´t use sp_helptext in the SP, need this permissions?

  • August 11, 2008 at 10:39 am

    #856232

    Has View Definition of the SP been explicitly denied?

    www.sql.lu
    SQL Server Luxembourg User Group

  • August 11, 2008 at 11:00 am

    #856254

    yes, but i not deny this object, only have execute permission

  • August 11, 2008 at 11:36 am

    #856272

    really i have this problem with SP

    Script failed for StoredProcedure 'dbo.usp_xxxxxxx'. (Microsoft.SqlServer.Smo)

    Additional informatio:

    Property TextHeader is not available dor StoreProcedure 'dbo.usp_xxxxxx'. This property may not exist dor this object, or may not be retrievable due to insufficient access rights. The text in encryted.

    thanks.

  • August 12, 2008 at 3:21 am

    #856548

    Hello,

    It sounds like the SP has been created with the Encryption option.

    What does sp_HelpText return for the SP?

    If the definition is encrypted then you drop and re-create the SP (without Encryption if you like), rather than Alter/Modify it.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • August 12, 2008 at 3:32 pm

    #857071

    Hi,

    The SP is not encrypted, but my error is in not grant permission VIEW DEFINITION.

    Thanks for you answers!!!!

  • August 13, 2008 at 2:12 am

    #857221

    Hello,

    Thanks for the update. It is always good to hear when an issue has been resolved.

    Best regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

Viewing 9 posts - 1 through 8 (of 8 total)

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