Post reply

User access to see stored procedure definitions

  • August 26, 2008 at 6:20 pm

    #192811

    We use SQL Server 2005 for most of our systems, development groups usually promoted code changes to a Dev, QA and Prod. Before in SQL 2000 as long as they had db_datareader access

    they are able to verify that code has been pushed thru by being able to script our objects.

    In SQL 2005 SSMS when they right click on a stored procedure, the "modify" button is grayed

    out. I know that there is a way to grant that access, hopefull someone can guide me in the

    right method.

    Thanks

  • August 26, 2008 at 9:52 pm

    #863485

    I think that you need to give them db_ddladmin.

    Of course, this may also give them the ability to modify the definitions.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 29, 2008 at 12:28 pm

    #865198

    To let them view without ability to change the sproc, how about creating a db role and granting permission to view definitions:

    -- Point to the DB with the sprocs to be viewed

    Use MyDB

    GO

    -- Manufacture the "view definition" DB role if it doesn't exist

    -- (ie: You can run this script a second time without erroring)

    -- The role name can be changed to one of your choosing

    IF NOT EXISTS (SELECT [name] FROM [sys].[sysusers] where [name] = 'db_SprocViewer')

    CREATE ROLE db_SprocViewer

    GO

    -- Grant permission "globally", including all future sprocs added later.

    -- This would also give permission to view the definitions of Views

    GRANT View Definition TO db_SprocViewer

    GO

    -- Add your db_datareader user to this role.

    EXEC sp_addrolemember 'db_SprocViewer','Login2ReadSprocs'

    GO

    Now, how do you guys format responses and put them in those cute, scrollable boxes?

  • August 29, 2008 at 12:52 pm

    #865212

    An alternate check that may or may not help in your situation (and is decent with encrypted sprocs) is to check the create or modified dates:

    Use MyDB

    SELECT name, create_date, modify_date

    FROM sys.objects where object_id =

    OBJECT_ID(N'schema.sprocName', N'P')

    GO

Viewing 4 posts - 1 through 3 (of 3 total)

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