List stored procedures/functions that ONLY read data RRS feed

  • Question

  • Hi,

    In order to make sure that user defined role has ONLY read access to the database, I need to grant execute permission to this role on all stored procedures/udfs that ONLY read (user databases) data. So I am not interested in db_datareader role, but only access controlled by stored procedures and functions.

    I was not able to find any flag that would tell us if sql module is changing data or not. Parsing sys.sql_modules doesn't seem like an option.

    Any ideas?



    Tuesday, February 7, 2017 7:24 PM

All replies

  • For UDF, you don't have to worry because, in UDF you cannot call other stored procedures and you cannot write insert/update/delete statements.

    From SQL Server Books Online:

    User-defined functions cannot be used to perform actions that modify the database state.

    For Stored Procedure, you have to write your own logic to check inside every stored procedure if it contains statements like insert/update/delete, etc.

    this may help:

    stackoverflow question 9421565 (sorry I can't embed href links somehow)

    Tuesday, February 7, 2017 7:43 PM