Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Force INSERT only via stored procedure

Using SQL Server 2008, is there a way to allow inserts to a table only via a stored procedure, and if so how?

EDIT:
The best way to go is probably Martin Smith's suggestion to use an INSTEAD OF INSERT trigger. The direct answer to this question is marc_s' one with GRANT and DENY, though it won't limit some user accounts.

like image 800
Asaf R Avatar asked Mar 28 '10 15:03

Asaf R


3 Answers

Just do not grant any database users (and your "public" role) the INSERT permission on the table.

Grant those users the permission to execute the INSERT stored proc - that way, they can call the stored proc, but they cannot directly insert any data into the underlying table.

DENY INSERT ON dbo.YourTable TO PUBLIC
GRANT EXECUTE ON dbo.InsertDataProc TO PUBLIC
like image 140
marc_s Avatar answered Oct 15 '22 10:10

marc_s


Deny INSERT permission on the table, and grant EXECUTE permission on the stored procedure.

like image 6
SLaks Avatar answered Oct 15 '22 08:10

SLaks


What is the motivation for the question? Is it because the stored procedure contains certain logic that you are relying on being carried out whenever data is inserted? If so you might want to use a trigger to encapsulate this logic.

If that isn't suitable and you want to prevent even people with sysadmin permissions from inserting to the table directly, well you can't, but you can make sure that it doesn't happen accidentally by using an INSTEAD OF INSERT trigger that raises an error and rolls back unless a certain CONTEXT_INFO value is set then clears the CONTEXT_INFO (Your stored procedure could set this to the expected value)

http://msdn.microsoft.com/en-us/library/aa214382%28SQL.80%29.aspx

like image 4
Martin Smith Avatar answered Oct 15 '22 08:10

Martin Smith