Function SyncLogins

recipes/Python/578977_Python_SQLSMO_/recipe-578977.py:111–139

(conn, db)

    return rows

def SyncLogins(conn, db):
    s = '''
    DECLARE @UserName nvarchar(255)
    DECLARE @SQLCmd nvarchar(511)
    DECLARE orphanuser_cur cursor for
    SELECT UserName = name
    FROM sysusers
    WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name
    OPEN orphanuser_cur
    FETCH NEXT FROM orphanuser_cur INTO @UserName
    WHILE (@@fetch_status = 0)
    BEGIN
    select @UserName + ' user name being resynced'
    set @SQLCmd = 'ALTER USER '+@UserName+' WITH LOGIN = '+@UserName
    EXEC (@SQLCmd)
    FETCH NEXT FROM orphanuser_cur INTO @UserName
    END
    CLOSE orphanuser_cur
    DEALLOCATE orphanuser_cur
    ''&#x27;
    # sqlcmd needs single line sql commands for the -Q option
    s = s.replace("\n", ' ')
    sqlsynclogins = 'SET NOCOUNT ON USE [' + db + '] ' + s
    rows, fnames = [], []
    try:
        rows, fnames = SqlExecute(conn, sqlsynclogins)
    except Exception as inst:
        print('Error executing fix logins: ', inst)
    return rows

def DatedString():
    """