November 30, 2011 at 3:05 am
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
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
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
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
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
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