May 032014
 

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())[1]', '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

 Posted by at 11:25 pm