SQL Server – How to Deny Drop Permission for a Table

Never a simple answer...

For a direct DELETE, a user in both roles won't be able to DELETE
DENY always has precedence when permissions are checked

For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining"

Personally, I don't really use DENY. Here's why:

In your case, you only need to GRANT DELETE to the Admin role. The Basic role needs neither DENY nor GRANT (but run REVOKE DELETE to remove the Basic DELETE permission)

• The lack of a DELETE permission (by REVOKE) means a DELETE will fail for a Basic only user. DENY isn't needed to prevent this.
• An Admin user (whether in both roles or not) has a GRANT so the DELETE will succeed. No DENY to block them

Use a DDL trigger to prevent the drop if the login name doesn't match what you expect. Modify the database name and login name as appropriate.

USE myDB
GO

CREATE TRIGGER no_drop ON DATABASE FOR DROP_TABLE
AS
IF SUSER_SNAME() != 'DOMAIN\username'
BEGIN
    RAISERROR('No table dropping.', 16, 1)
    ROLLBACK TRANSACTION
END