permissions to view stored procedures

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  September 6, 2011 at 12:57 pm

  #243331

  I have a developer want to view all stored procedures in a database.

  I assign her the data reader role in the database, will he see the stored procedure?

  If not, what permission can I assign her?

  Thanks

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 6, 2011 at 1:03 pm

  #1377641

  you'll want to give VIEW DEFINITION to them;

  --at the database / schema level

  Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

  --at the database level

  Grant View Definition To [AlmostOwners]

  --at the Login level in the master database

  Grant View ANY Definition To [mydomain\SQLfriends]

  that lets them see the definitions of all procs, views, functions, etc.

  if you weant to do just procs and not views, for example, you have to grant at the per-object level for each of the objects in question instead.

  GRANT VIEW DEFINITION ON MyProc TO SomeUser

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!