locked
SQL 2008 grant permissions to specific user to view all stored procs within db but not alter, create or delete? RRS feed

  • Question

  • Hello what is the recommended way of doing this?  Thanks in advance.
    Thursday, March 22, 2012 6:56 PM

Answers

  • You can grant them view access using GRANT VIEW DEFINITION to schema or individual entities .

    Thanks, Leks

    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 3:33 AM
    • Marked as answer by Iric Wen Sunday, April 1, 2012 1:37 PM
    Thursday, March 22, 2012 8:07 PM
  • Yes, VIEW ANY DEFINITION would imply VIEW DEFINITION in all databases (unless overridden by DENY). VIEW ANY DEFINITION may also give access to server-level objects.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 3:32 AM
    • Marked as answer by Iric Wen Sunday, April 1, 2012 1:37 PM
    Thursday, March 22, 2012 10:46 PM

All replies

  • it depends on what permission the user effectively need or which he must not have.

    If he is allowed to read data directly you can give him data_reader permission.

    if he is allowed  to read and change data: data_reader, data_writer permission

    if he is only allowed to access the database by stored procedures: grant him execute permission on the stored procs

    Thursday, March 22, 2012 7:59 PM
  • You can grant them view access using GRANT VIEW DEFINITION to schema or individual entities .

    Thanks, Leks

    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 3:33 AM
    • Marked as answer by Iric Wen Sunday, April 1, 2012 1:37 PM
    Thursday, March 22, 2012 8:07 PM
  • Thanks for both of your speedy replies, really appreciate it.  If a user is in the db_datareader & db_datawriter roles and just needed to view all objects & their definitions but not alter, create or delete like mentioned at the database level would:  GRANT VIEW DEFINITION TO [Fred]    be appropriate?   Which using the ANY command as GRANT VIEW ANY DEFINITION TO [Fred]   would be at the entire server scope level in seeing all object definitions in all dbs?
    Thursday, March 22, 2012 10:20 PM
  • Yes, VIEW ANY DEFINITION would imply VIEW DEFINITION in all databases (unless overridden by DENY). VIEW ANY DEFINITION may also give access to server-level objects.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 3:32 AM
    • Marked as answer by Iric Wen Sunday, April 1, 2012 1:37 PM
    Thursday, March 22, 2012 10:46 PM