Force INSERT only via stored procedure

Question

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.

Answer 1

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

Answer 2

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

Answer 3

Use permissions (grants). Create the SP and grant the execute right to the user or role in question. Then deny the insert right on the table an you should be all set.

Answer 4

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 acciddentally 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