I recently spent a while at work cleaning up the permissions on a database. Some of the steps followed:
- All users are now assigned to a role
- Appropriate permissions were given to the roles
- All permissions (except for connect) were removed from the users
Now that I spent all of the time and energy getting the roles setup and getting the development team to agree to and understand how and why we wanted everyone assigned to a role, I wanted to ensure that no future permissions get given directly to users. To that end I wrote this trigger to revert any permissions that someone may try to give. It is still easy to turn off but I wanted to give whomever was assigning the rights another chance to reconsider if this is what they really wanted to do.
CREATE TRIGGER PermissionTrigger ON DATABASE FOR GRANT_DATABASE AS BEGIN --Does a check if the permissions are being assigned to a user (Both SQL and Windows) IF EXISTS ( SELECT 1 FROM ( --List of Grantee's from the EventData SELECT Grantee.value('(text())', 'nvarchar(128)') AS PrincipalName FROM (SELECT EVENTDATA() AS XMLData) x CROSS APPLY x.XMLData.nodes('//EVENT_INSTANCE/Grantees/Grantee') AS Grantees(Grantee) ) G JOIN sys.database_principals dp ON dp.NAME = g.PrincipalName WHERE dp.type IN ('S','U') --SQL Users and Windows Users ) BEGIN RAISERROR ('You can not assign permissions to a user. If you think it is in error please contact a DBA',10,1) ROLLBACK END END
Edit: 2017-03-20 script is now on github