User access to see stored procedure definitions

• oliver ville

  SSC Veteran

  Points: 201

  More actions

  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

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  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]

• dcorbitt

  Ten Centuries

  Points: 1006

  More actions

  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?

• dcorbitt

  Ten Centuries

  Points: 1006

  More actions

  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