Search code examples
sqlpermissionssql-grant

Clear/remove all GRANT and DENY of a user in SQL


I see a lot of GRANT and DENY records in table sys.database_permissions for a specific user.

How can I delete/clear all these entries? I need to clear to run a script that will do the appropriate GRANT and DENY.

I can't simply delete the user and recreate. This view does not accept modifications, can't simply run a DELETE.

Thank you.


Solution

  • Seems there is no better way than doing it manually:

    DECLARE cursor_roles CURSOR LOCAL FAST_FORWARD FOR 
        SELECT dbrole.name as role_name, dbuser.name as user_name
        FROM sys.database_role_members rm
        INNER JOIN sys.database_principals dbrole ON  dbrole.principal_id = rm.role_principal_id
        INNER JOIN sys.database_principals dbuser ON  dbuser.principal_id = rm.member_principal_id
        WHERE dbuser.name in (@MYUSER)
    
    OPEN cursor_roles
    DECLARE @role_name nvarchar(100), @user_name_in_role nvarchar(100)
    FETCH NEXT FROM cursor_roles INTO @role_name, @user_name_in_role
    WHILE @@FETCH_STATUS = 0  
    BEGIN
        EXEC('ALTER ROLE ' + @role_name + ' DROP MEMBER ' + @user_name_in_role)
    
        FETCH NEXT FROM cursor_roles INTO @role_name, @user_name_in_role
    END
    
    CLOSE cursor_roles  
    DEALLOCATE cursor_roles