SQL Login Replication

SQL-Login-Replication.sql

SET NOCOUNT ON;
DECLARE @name sysname,
        @PWD_varbinary varbinary (256),
        @PWD_string varchar (514),
        @SID_varbinary varbinary (85),
        @SID_string varchar (514),
        @sqlString varchar (1024),
        @is_policy_checked varchar (3),
        @is_expiration_checked varchar (3),
        @defaultdb sysname;
DECLARE @logins TABLE (SID varbinary(256), SQL varchar (1024), DefaultDB sysname);
 
DECLARE login_cursor CURSOR FOR
    SELECT *
      FROM OPENQUERY(/*[<LinkedServerNameHere>]*/, '
    SELECT p.sid, p.name, p.default_database_name, 
           CAST(l.password AS varbinary (256)) pwd_varbinary,
           CASE sl.is_policy_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END is_policy_checked,
           CASE sl.is_expiration_checked WHEN 1 THEN ''ON'' WHEN 0 THEN ''OFF'' ELSE NULL END is_expiration_checked
    FROM sys.server_principals p 
         JOIN sys.syslogins l ON l.name = p.name
         JOIN sys.sql_logins sl ON l.name = sl.name
    WHERE p.type = ''S''
      AND p.name <> ''sa''
      AND l.denylogin = 0
      AND l.hasaccess = 1
      AND p.is_disabled = 0
    ORDER BY p.name')
OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @SID_varbinary, @name, @defaultdb, @PWD_varbinary, @is_policy_checked, @is_expiration_checked
WHILE @@fetch_status = 0
BEGIN
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
     
    SET @sqlString = 'CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
        + ', CHECK_POLICY = ' + @is_policy_checked
        + ', CHECK_EXPIRATION = ' + @is_expiration_checked
    INSERT INTO @logins (SID, SQL, DefaultDB) VALUES (@SID_varbinary, @sqlString, @defaultdb);
    FETCH NEXT FROM login_cursor INTO @SID_varbinary, @name, @defaultdb, @PWD_varbinary, @is_policy_checked, @is_expiration_checked
END
CLOSE login_cursor
DEALLOCATE login_cursor

DECLARE @sql varchar (1024), @db sysname;
DECLARE login_cursor CURSOR FOR 
    SELECT SQL, DefaultDB
      FROM @logins 
     WHERE SID NOT IN (SELECT sid FROM sys.server_principals)
       AND EXISTS (SELECT 1 FROM sys.databases WHERE name = DefaultDB)
OPEN login_cursor

FETCH NEXT FROM login_cursor INTO @sql, @db
WHILE @@fetch_status = 0
BEGIN
    PRINT @sql;
    --EXEC(@sql);
    FETCH NEXT FROM login_cursor INTO @sql, @db
END
CLOSE login_cursor
DEALLOCATE login_cursor