I'm having trouble with a SQL DDL trigger for Create_User and I'm wondering if there is an issue with my syntax or something else I'm not seeing?
I have some tables in my database which are only available to a few users. The rest of the users in the database are not allowed to query these tables in any way. I've had no problems limiting current user permissions with
DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO UserName
I'm trying to set up a DDL trigger to automatically run the Deny command when any new users are created. I am not the only one creating users in the database, so I won't be able to manually set permissions myself when new users are added.
With my trigger, I constantly receive the error
Cannot find the user 'UserName', because it does not exist or you do not have permission.
If I change the UserName placeholder to an already existing user in the trigger, I do not receive any errors - so it is not a permissions issue for my account. Here is my trigger:
CREATE TRIGGER [NewUserCreation]
ON DATABASE
After Create_User
AS
BEGIN
SET NOCOUNT ON;
DECLARE @x xml = EVENTDATA();
SELECT UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
DENY SELECT, INSERT, UPDATE, DELETE, ON dbo.SecretTable TO [UserName]
END
GO
ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO
Is there something I'm missing? The test results I've experienced so far seem to imply that the trigger is firing before the user account is actually created.