SQL Server security via TSQL

Question

I want to construct a transact sql script that will stop specified people from running certain commands against all databases:

drop database, drop table or preferbly drop *

delete

update

Is this possible? The user will already have access to the server.

Note : I am not trying to develop a security model for a server, or to prevent a malicious attack. This is an existing server where people may have a range of access rights through various Windows groups they belong to. I just want to know if there is a quick safeguard to prevent people from mistakenly running a command on the wrong server.

Answer 1

(caveat, this is per-database; I don't know of anything server-wide, since the database is the main standalone unit)

Presumably your user isn't the owner of the schema (or dbo)? In which case, they already shouldn't have access to, well, anything unless you GRANT it. So don't GRANT the access they don't need, REVOKE any access you have granted incorrectly, and DENY anything that you absolutely don't want them ever being able to do.

See also MSDN.