Post reply

error creating trigger

  • August 27, 2016 at 8:27 am

    #320925

    I created a database, but when I run a script to create a trigger, I get error:

    Cannot create the trigger 'MyDDLTrigger', because you do not have permission.

    what permissions do I need to set to create trigger ?

    Here is part of the script that seems to be causing the error:

    CREATE TRIGGER [MyDDLTrigger]

    ON ALL SERVER

    WITH EXECUTE AS SELF

    FOR DDL_DATABASE_LEVEL_EVENTS

    AS

    ...............

    Go

    Thanks.

  • August 27, 2016 at 12:13 pm

    #1897958

    https://msdn.microsoft.com/en-za/library/ms189799.aspx

    Permissions

    To create a DML trigger requires ALTER permission on the table or view on which the trigger is being created.

    To create a DDL trigger with server scope (ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission on the server. To create a DDL trigger with database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply