数据库镜像中记录login的变化并同步

数据库镜像的方案中，login是server级的对象，不能被同步，这对我们来说有些麻烦，这个触发器可以记录下变更的记录，便于手工活自动地同步用户信息

alter TRIGGER trg_logins ON ALL SERVER

FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN

as

begin

DECLARE @data XML;

DECLARE @action varchar(30);

DECLARE @loginname nvarchar(128);

DECLARE @owner nvarchar(128);

DECLARE @logintype varchar(100);

DECLARE @stmt varchar(1024);

DECLARE @s1 varchar(1024);

 

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @defaultdb sysname

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @fs int;

DECLARE @P1 int;

DECLARE @P2 int;

 

SELECT @P1 = mirroring_role

FROM sys.database_mirroring WHERE database_id =

(select database_id from sys.databases where name='yourDBname')

 

IF (@P1 = 2) return;

 

SET @data = EVENTDATA();

set @loginname = @data.value('(//EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)' );

set @owner = @data.value('(//EVENT_INSTANCE/LoginName)[1]','nvarchar(1128)' );

set @action = @data.value('(//EVENT_INSTANCE/EventType)[1]','varchar(30)' );

set @logintype = @data.value('(//EVENT_INSTANCE/LoginType)[1]','varchar(100)' );

 

IF ((@action = 'CREATE_LOGIN') and (@logintype = 'SQL Login'))

BEGIN

 

    DECLARE login_curs CURSOR local FOR

      SELECT default_database_name, sid, password_hash,

               CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END,

               CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

      FROM sys.sql_logins

      WHERE name = @loginname;

      OPEN login_curs;

      FETCH NEXT FROM login_curs INTO @defaultdb, @SID_varbinary, @PWD_varbinary, @is_policy_checked, @is_expiration_checked

      set @fs = @@fetch_status;

      CLOSE login_curs

      DEALLOCATE login_curs

      IF (@fs = -1) RETURN;

         

      set @PWD_string =  CONVERT(varchar(max), @PWD_varbinary, 1);

      set @SID_string =  CONVERT(varchar(max), @SID_varbinary, 1);

        

      SET @stmt = 'Create Login ' + QUOTENAME( @loginname) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string;

      SET @stmt = @stmt +' ,DEFAULT_DATABASE =' + QUOTENAME( @defaultdb);

        

       

      IF ( @is_policy_checked IS NOT NULL )

      BEGIN

              SET @stmt = @stmt + ', CHECK_POLICY = ' + @is_policy_checked

      END

      IF ( @is_expiration_checked IS NOT NULL )

      BEGIN

              SET @stmt = @stmt + ', CHECK_EXPIRATION = ' + @is_expiration_checked

      END

     

END ELSE

BEGIN

    set @stmt = @data.value('(//EVENT_INSTANCE/TSQLCommand)[1]','varchar(1024)' );

    set @p1 = CHARINDEX ( 'password',@stmt COLLATE Latin1_General_CI_AS);

    set @p2 = CHARINDEX ( '=',@stmt,@p1);

    set @s1 = Substring(@stmt,@p1,@p2 - @p1 );

    print @s1

    set @s1 = replace(@s1,CHAR(9),'');

    set @s1 = replace(@s1,CHAR(10),'');

    set @s1 = replace(@s1,CHAR(13),'');

    set @s1 = RTRIM(@s1);

    print @s1;

    print len(@s1);

    if (lower(@s1) = 'password')

    begin

        print 'Old SQL:'+@stmt;

            DECLARE login_curs2 CURSOR LOCAL FOR

            SELECT default_database_name, password_hash,

                     CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END,

                     CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

            FROM sys.sql_logins

            WHERE name = @loginname;

 

          OPEN login_curs2;

            FETCH NEXT FROM login_curs2 INTO @defaultdb, @PWD_varbinary, @is_policy_checked, @is_expiration_checked

            set @fs = @@fetch_status;

            CLOSE login_curs2;

            DEALLOCATE login_curs2;

            IF (@fs = -1) RETURN;

               

            set @PWD_string =  CONVERT(varchar(max), @PWD_varbinary, 1);

              

            SET @stmt = 'alter Login ' + QUOTENAME( @loginname) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED';

            SET @stmt = @stmt+', DEFAULT_DATABASE =' + QUOTENAME( @defaultdb)+ ', CHECK_POLICY =OFF';

             

            IF ( @is_expiration_checked IS NOT NULL )

            BEGIN

                   SET @stmt = @stmt + ', CHECK_EXPIRATION = ' + @is_expiration_checked

            END

            IF ( @is_policy_checked ='ON' )

            BEGIN

                   SET @stmt = @stmt+';alter login [' + @loginname + '] with CHECK_POLICY = ON';

            END

 

    end;

END;

Insert into ddl_logs (stmt,flag,owner,ddl_date)

values (@stmt,'I',@owner,GETDATE());

END;