Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Dec 21 '11 at 11:52

This question came from our site for professional and enthusiast programmers.

You are limited in because they are admins. You should separate groups in AD first and do it properly end to end – gbn Dec 21 '11 at 13:42

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.


IF SUSER_SNAME() != 'DOMAIN\username'
    RAISERROR('No table dropping.', 16, 1)
share|improve this answer
Being admins, they could just disable or alter the DDL trigger once they spot it. – BBlake Dec 21 '11 at 13:45
Naturally. And if you don't trust them to not skirt the safety checks, then they shouldn't be administrators in the first place. – db2 Dec 21 '11 at 13:49
:) Being a SQL Server developer myself, I can't count the number of times I have had to skirt the safety checks put in place by the DBAs in order to get my job done. – BBlake Dec 21 '11 at 13:55

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];

    '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,


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.