clintonG (3/16/2013)
Here is one solution perhaps the best I've learned so far:// Transact-SQL
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}
// Example
REVOKE DELETE
ON TransactionRecordsTable
FROM employee
REVOKE only clears the granted permission, a user may still obtain this via another role,etc.
clintonG (3/16/2013)
Disallow a specific User type the permission to DELETE any type of object in a specific database
You could grant the user insert and update permission on the schema the objects reside in.
clintonG (3/16/2013)
including disallowing deleting the database itself.
No need to fear here as this requires a high level of elevated privilege. The user must have at least CONTROL on the database or be a member of DB_Owner role or SYSADMIN role, as long as you're not granting this then that's fine.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉