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

Question

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.

Answer 1

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

Answer 2

This will generate the code to revoke DELETE permissions, which includes drop object permissions. You will need to add your own filtering to make it work in your environment. TEST THIS IN A TEST ENVIRONMENT FIRST!

USE [myDB];
GO

SELECT DISTINCT
    'REVOKE ' + 
    [dperm].[permission_name] COLLATE database_default + 
    ' ON [' + 
    [s].[name] + 
    '].[' + 
    [o].[name] + 
    '] TO [' + 
    [dp].[name] +
    '];' AS [sql_statement]
FROM [sys].[database_principals] AS [dp]

    INNER JOIN [sys].[database_permissions] AS [dperm]
        ON [dp].[principal_id] = [dperm].[grantee_principal_id]

        INNER JOIN [sys].[objects] AS [o]
            ON [dperm].[major_id] = [o].[object_id]

            INNER JOIN [sys].[schemas] AS [s]
                ON [o].[schema_id] = [s].[schema_id]

        LEFT OUTER JOIN [sys].[columns] AS [c]
            ON [dperm].[major_id] = [c].[object_id]
            AND [dperm].[minor_id] = [c].[column_id]

WHERE [dp].[name] <> 'Administrator'  --Change this to reflect your Administrator
AND [dperm].[state] = 'G'
AND [dperm].[type] = 'DL'

Hope this helps,

Matt