Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com 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

2 Answers 2

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

share|improve this answer

Your Answer

 
discard

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