2

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.

2

I think you'll have to use dynamic SQL for this. Something like

create TRIGGER [NewUserCreation] ON DATABASE 
After Create_User
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();   
    declare @UserName Varchar(30)
    SELECT @UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
    declare @Cmd nvarchar(100)
    set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
    exec sp_executesql @cmd
END
GO

ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO

The reason your original code did not work is because your select username merely gave an alias UserName to the value returned from the select. You are not able to use that column alias in the DENY. Note that my example puts the value into a variable @UserName. Then, I build a dynamic DENY statement and externalize the value of @UserName which is concatenated to the DENY command.

You can see this in action with this code snippet. Assume I'm adding NewUser as a login and that is the value from the xml.

Declare @UserName varchar(100)
SELECT @UserName = (select 'NewUser')
declare @Cmd nvarchar(100)
set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
print @cmd

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO [NewUser]

| improve this answer | |
  • That was it! Thanks for the help Scott. Is there a reason why dynamic sql worked here instead of a straight sql command? – Eric MPC Jan 31 '19 at 17:54
  • 1
    @Eric - I added some extra information to my answer that will hopefully answer your question. – Scott Hodgin Jan 31 '19 at 18:12
  • Ah that makes sense, thanks again. Previously I tried putting the value into a variable @UserName but experienced the same result. Building the dynamic query and externalizing the value was the part I was missing. – Eric MPC Jan 31 '19 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.