November 9, 2007 at 9:32 am
We are trying to build a model for locking down databases to only allow stored procedure execution (no datawriter, no datareader). We are building Access adp front ends and are binding the data to stored procedures and views. On the forms we want to allow updates to the record without unbinding the fields and without executing a separate procedure.
It seems that no matter how I configure this I need to grant datawriter to the user. Can this be done in sql server 2000? Granted, I wouldn't say that I am well versed in role management but I can generally figure stuff like this out on my own.
Any information, guidance or referrals to resources would be greatly appreciated.
November 10, 2007 at 10:36 pm
In SQL Server 2000 there is a concept called ownership chaining. If the stored procedure and the object it refers to (such as a table or view) are both owned by the same user, security is only checked on the stored procedure. As long as the incoming user has execute rights on the stored procedure, that's all that is needed.
The exception is if the stored procedure uses dynamic SQL such as through EXEC(UTE) or sp_executesql. This causes the SQL to execute in a different batch and in that case the permissions are rechecked.
So if you've got the objects all owned by the same user, then you should only need to grant EXECUTE rights on the stored procedures, as far as SQL Server is concerned. You shouldn't need db_datawriter role membership.
K. Brian Kelley
@kbriankelley
September 15, 2010 at 2:52 pm
So if I too can ask for help on the coattail of this thread... I have created multiple stored procs that used the sp_executesql method to write data. We recently secured the database properly (the executing ID does not have data writer) and now many of the transactions are failing.
I still need to achieve the end result of executing a stored procedure that results in a simple "UPDATE Table1 Set Feild1 = 'Value1'" type of transaction for an ID that does not have datawriter permissions (but has execute permission)...
What would you recommend to be the proper way of building a stored procedure to accomplish this task? Any examples would be greatly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply