SQL Server 2008 - Delete data exclusively from stored procedure

I would like to stop power users from deleting data using SQL Server Management Studio. I need to archive data and add some info to the audit trail when data gets deleted.

• Is there a way to stop them when they attempt to delete the data from SSMS?
• Is there a way to know whi开发者_如何学编程ch process caused the deletion? such as from SSMS, application, stored proc?
• Is there a way to allow only deletes from Stored Procedure?

Thanks

Create a new login and database user for this login. Then grant delete permission to this user, and revoke it from all others. Write procedure[s] that removes data, add WITH EXECUTE AS [previously created user that can delete data]. Grant other users with execute permissions to the procedure[s].

Well they are power users aren't they. You could set deny Delete permissions for them.

DENY DELETE TO [Your_User]
GO

From your second sentence I get the impression that deleting is not the real issue but that you need to archive data and create an audit trail when data is deleted. Why not use a delete trigger?