INFORMATION_SCHEMA.ROUTINES permissions

• Simon D Richards

  Ten Centuries

  Points: 1048

  More actions

  September 30, 2010 at 6:25 am

  #224571

  Hi All, I have a manager that needs to look at the definitions of a set of procs (about 175 of them) to pull the business logic out as it is undocumented.

  I have created a report that puts all of the procs into a drop down so he can select them and then look at the definition using SQL as below:

  SELECT

  specific_name,

  ROUTINE_DEFINITION

  FROM

  INFORMATION_SCHEMA.ROUTINES

  WHERE

  specific_name like '%update%' + CONVERT(VARCHAR(5),@except_no)

  When I run this I get the definition just fine, when the report user runs this they get a NULL for the routine_definition. I have tried just granting the permissions to the view, and to the underlying tables, but the problem persists. I would prefer not to give the report user ddladmin privileges.

  Is there a way around this?

  Thanks in advance.

• DB_Newbie2007

  SSCarpal Tunnel

  Points: 4446

  More actions

  November 19, 2014 at 12:04 pm

  #1759602

  I was investigating the same issue and discovered that you need to do the following, when the login does not have dbo or sa permissions:

  GRANT VIEW DEFINITION ON [Name of Procedure] TO [User/Role];

  See BOL for full details:

  http://technet.microsoft.com/en-us/library/ms188371%28v=sql.105%29.aspx

  Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)