Post reply

EXECUTE sproc permissions

  • November 30, 2011 at 3:05 am

    #252784

    If I assign EXECUTE permissions to a stored procedure that just selects data from a table, does the login also need db_datareader or is EXECUTE enough?

  • November 30, 2011 at 3:18 am

    #1414522

    You don't need to assign permissions on the underlying tables and views. This is one great advantage of developing your application so that all access is through stored procedures instead of directly to tables.

    John

  • November 30, 2011 at 3:24 am

    #1414529

    Yes I thought so - just having some issues with a spreadsheet accessing the data but I think it's the connectionstring that is incorrect.

  • December 8, 2011 at 9:52 am

    #1418794

    John Mitchell-245523 (11/30/2011)

    You don't need to assign permissions on the underlying tables and views. This is one great advantage of developing your application so that all access is through stored procedures instead of directly to tables.

    John

    Be careful...this is true only when the proc is owned by the same entity as all objects (tables, views, other procs, functions, etc.) referenced within the proc. This is the case when all objects exist in the dbo schema and are owned by dbo, which is by far the most common scenario, however if user-defined schemas are in place or you are crossing a database boundary the game changes.

    @hindle.steve, if the proc owner is different from the table owner or the table is in a different database from the proc then the caller of the proc will require SELECT permission on the table. I would recommend staying away from putting users into the db_datareader Role as well as avoiding direct table-level SELECT permissions. I strive to provide data access exclusively through VIEW, PROC or FUNCTION objects, i.e. never directly from a TABLE.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 8, 2011 at 9:58 am

    #1418802

    thanks for the explanation. so if the proc owner is different from the table owner for example then the caller will have to have SELECT permissions on the underlying table?

  • December 8, 2011 at 10:52 am

    #1418829

    hindle.steve (12/8/2011)

    thanks for the explanation. so if the proc owner is different from the table owner for example then the caller will have to have SELECT permissions on the underlying table?

    That is correct. Are you operating in a single database or across two or more database boundaries?

    Keep in mind that objects in different schemas can have the same owner, and similarly objects in the same schema can have different owners too. You can check object owners like this:

    DECLARE @object_name SYSNAME = N'schema_name.table_name'

    SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID(@object_name), 'ownerid')) AS 'object_owner'

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply