1

I'm using SSDT database project to create deployment scripts for my database. One of the trigger, [trddl_GrantUserRights] is re-alter every time when the deployment runs. Nothing has changed in the definition of the trigger in the project files. Definition of the trigger:

ALTER trigger [trddl_GrantUserRights] ON DATABASE 
WITH EXECUTE AS 'dbo'

FOR DDL_DATABASE_LEVEL_EVENTS 

AS 

BEGIN


SET NOCOUNT ON;

    DECLARE @Cmd varchar(250)           

    /* InterfaceDeveloperUsers ve InterfaceDeveloperUsers rolleri db_datareader ve db_datawriter rollerini kapsadığından bu bölüme gerek kalmamıştır.

    IF  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')='CREATE_TABLE' 
        OR EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')='CREATE_VIEW'
    BEGIN

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceAnalistUsers' AND type = 'R')  
        BEGIN
            SET @Cmd = 'GRANT SELECT,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceAnalistUsers]'
            EXEC (@Cmd)
        END

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceDeveloperUsers' AND type = 'R')  
        BEGIN
            SET @Cmd = 'GRANT SELECT,UPDATE,DELETE,INSERT,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceDeveloperUsers]'
            EXEC (@Cmd)
        END
    END
    */

    IF  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')='CREATE_PROCEDURE'
    BEGIN

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceAnalistUsers' AND type = 'R')  
        BEGIN
            SET @Cmd = 'GRANT VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceAnalistUsers]'
            EXEC (@Cmd)
        END

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceDeveloperUsers' AND type = 'R')  
        BEGIN
            SET @Cmd = 'GRANT EXECUTE,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceDeveloperUsers]'
            EXEC (@Cmd)
        END

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PusulaUser' AND type = 'S')  
        BEGIN
            SET @Cmd = 'GRANT EXECUTE ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [PusulaUser]'
            EXEC (@Cmd)
        END
    END


    IF  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')='CREATE_FUNCTION'

    BEGIN

        IF  EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)')='TF'
        BEGIN
            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceAnalistUsers' AND type = 'R')  
            BEGIN
                SET @Cmd = 'GRANT SELECT,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceAnalistUsers]'
                EXEC (@Cmd)
            END

            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceDeveloperUsers' AND type = 'R')  
            BEGIN
                SET @Cmd = 'GRANT SELECT,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceDeveloperUsers]'
                EXEC (@Cmd)
            END

            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PusulaUser' AND type = 'S')  
            BEGIN
                SET @Cmd = 'GRANT SELECT ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [PusulaUser]'
                EXEC (@Cmd)
            END
        END             

        IF  EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)')='FN'
        BEGIN
            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceAnalistUsers' AND type = 'R')  
            BEGIN
                SET @Cmd = 'GRANT VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceAnalistUsers]'
                EXEC (@Cmd)
            END

            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceDeveloperUsers' AND type = 'R')  
            BEGIN
                SET @Cmd = 'GRANT EXECUTE,VIEW DEFINITION ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [InterfaceDeveloperUsers]'
                EXEC (@Cmd)
            END

            IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'PusulaUser' AND type = 'S')  
            BEGIN
                SET @Cmd = 'GRANT EXECUTE ON  [' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + '].[' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)') + '] TO [PusulaUser]'
                EXEC (@Cmd)
            END
        END

    END

    IF  EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')='CREATE_SCHEMA'

    BEGIN

        IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'InterfaceDeveloperUsers' AND type = 'R')  
        BEGIN
            SET @Cmd = 'GRANT ALTER ON SCHEMA::' + EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') + ' TO [InterfaceDeveloperUsers]'
            EXEC (@Cmd)
        END

    END 

END;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Tahir
  • 95
  • 1
  • 9
  • And what is your question? – Dale K Oct 07 '19 at 08:00
  • i don't want it to occur continuously in script file – Tahir Oct 07 '19 at 08:23
  • If you *don't* take care of the differences the trigger's change script will always be included in the script. Comment and whitespace differences count too. Use `Schema Compare` between your database and project file to see what the differences are. – Panagiotis Kanavos Oct 07 '19 at 08:27
  • First of all make sure that there is no any non unicode symbols in the code. Another thing to try is to make database compare and try to update this trigger from the database to the project. – Dmitrij Kultasev Oct 08 '19 at 04:47

0 Answers0