Sql-server – Deny drop permissions for all other users except actual administrator – SQL Server 2008

sql-server-2008

I have a database "myDB" running in a Windows Server machine. Users (coming under Administrators group) remotely access this server and log-in to this database using Windows Authentication mechanism.

I want to deny the drop object permissions for all these users, except for the actual Administrator in this machine

P.S – All the users whose drop permission is to be revoked comes under the Administrators user group.

Best Answer

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
Related Question