• Replies 5 replies
  • Subscribers 337 subscribers
  • Views 28 views
  • Users 0 members are here
Related

Tessitura Naming Issues

Matt Gonzales
$organization

As it occasionally happens, one of our users got married.  She has also become a solicitor, which necessitated the appropriate change in the Tessitura Security module from USER to BOTH.  However, it occurs to me that someone will soon call to say "Hey, can we change her last name too?"  Being a database guy, I figured there are likely greater implications at work here, so I'm posing this question to you all.  Can I just change the user login (from MFORD to MHAYS) and last name in the security module to reflect her new status or will Tessitura freak out?  I suspect it's not that easy...

Thank you in advance, Tessiturians.

BONUS NAME-RELATED TRIVIA:   The Comte de la Fere, the Baron du Vallon, and the Chevalier d'Herblay are better known in literature as The Three Musketeers.

Parents
  • $organization

    Hi Matt, I've got a bit of SQL that will change ownership of everything from one user name to another user name. Probably isn't the most elegant way of doing it, but it defianately works.

    Debbie

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    -------------------------------------------------------------------

    --CODE WILL CHANGE EVERYTHING IN THE DATABASE WHICH BELONGS TO Username_Old to Username_New

    -------------------------------------------------------------------

    use

     

     

    impresario

    go

    DECLARE

     

     

    ChangeCursor CURSOR FOR

    (

     

     

    SELECT DISTINCT table_name,

    column_name

     

     

    FROM

    INFORMATION_SCHEMA.columns

     

     

    WHERE column_name IN ('userid', 'created_by', 'last_modified_by'

    )

     

     

    AND (table_name LIKE 'T%' OR table_name LIKE 'L%'

    ))

    DECLARE

     

     

    @Table_Name VARCHAR(255

    )

    DECLARE

     

     

    @Column_Name VARCHAR(255

    )

    DECLARE

     

     

    @Username_Old VARCHAR(10

    )

    DECLARE

     

     

    @Username_New VARCHAR(10

    )

    DECLARE

     

     

    @SQL VARCHAR(4000

    )

    DECLARE

     

     

    @RC

    INT

     

    SET

     

     

    @Username_Old = 'Debs'

    --'XXXXXXXXXX' --

    SET

     

     

    @Username_New = 'DebbieH'

    --'XXXXXXXXXX' --

    OPEN

     

     

    ChangeCursor

    FETCH

     

     

    NEXT FROM ChangeCursor INTO @Table_Name,

    @Column_Name

    SET

     

     

    @SQL = 'Update ' + @Table_name + ' SET ' + @Column_name +

     

     

    ' = ''''' + @Username_New + ''''' WHERE ' + @Column_Name + ' = ''''' +

    @Username_Old

     

    +

    ''''''';'

    WHILE

     

     

    @@FETCH_STATUS =

    0

    BEGIN

     

     

    EXEC ('DECLARE @RC INT;'

    +

     

     

    'SELECT @RC=COUNT(*) FROM ' + @Table_Name + ' WHERE ' + @Column_Name +

     

     

    ' = ''' + @Username_Old + ''';'

    +

     

     

    'IF @RC > 0 PRINT ''UPDATE ' + @Table_Name + ' SET ' + @Column_Name +

     

     

    ' = ''''' + @Username_New + ''''' WHERE ' + @Column_Name + ' = ''''' +

    @Username_Old

     

    + ''''''';'

    )

     

     

    FETCH NEXT FROM ChangeCursor INTO @Table_Name,

    @Column_Name

     

     

    IF @@FETCH_STATUS <> 0

    BREAK

    END

    CLOSE

     

     

    ChangeCursor

    DEALLOCATE

     

     

    ChangeCursor

     

    -----------------------------------------------------

    DECLARE

     

     

    @SQL VARCHAR(4000

    )

    DECLARE

     

     

    @Table_Name VARCHAR(255

    )

    DECLARE

     

     

    @Column_Name VARCHAR(255

    )

    DECLARE

     

     

    @Username_Old VARCHAR(10

    )

    DECLARE

     

     

    @Username_New VARCHAR(10

    )

    SET

     

     

    @Username_Old =

    'Debs'

    SET

     

     

    @Username_New =

    'DebbieH'

    SET

     

     

    @TABLE_NAME =

    'T_METUSER'

    SET

     

     

    @Column_name =

    'userid'

    print

     

     

    'UPDATE ' + @Table_name +

     

     

    ' SET ' + @Column_name +

     

     

    ' = ' + @Username_New +

     

     

    ' WHERE ' + @Column_Name +

     

     

    ' = ' +

    @Username_Old

    PRINT

     

     

    @SQL

     

     

    -------------------------------------------------------------

    USE

     

     

    impresario

    ;

    GO

    DECLARE

     

     

    tables_cursor

    CURSOR

     

     

    FOR

     

     

    SELECT s.name, t.name

     

     

    FROM sys.objects AS

    t

     

     

    JOIN sys.schemas AS s ON s.schema_id = t.

    schema_id

     

     

    WHERE t.type = 'U'

    ;

    OPEN

     

     

    tables_cursor

    ;

    DECLARE

     

     

    @schemaname sysname

    ;

    DECLARE

     

     

    @tablename sysname

    ;

    FETCH

     

     

    NEXT FROM tables_cursor INTO @schemaname, @tablename

    ;

    WHILE

     

     

    (@@FETCH_STATUS <> -1

    )

    BEGIN

     

     

    ;

     

     

    EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;'

    );

     

     

    FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename

    ;

    END

     

     

    ;

    PRINT

     

     

    'The indexes on all tables have been rebuilt.'

    ;

    CLOSE

     

     

    tables_cursor

    ;

    DEALLOCATE

     

     

    tables_cursor

    ;

    GO

Reply
  • $organization

    Hi Matt, I've got a bit of SQL that will change ownership of everything from one user name to another user name. Probably isn't the most elegant way of doing it, but it defianately works.

    Debbie

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    -------------------------------------------------------------------

    --CODE WILL CHANGE EVERYTHING IN THE DATABASE WHICH BELONGS TO Username_Old to Username_New

    -------------------------------------------------------------------

    use

     

     

    impresario

    go

    DECLARE

     

     

    ChangeCursor CURSOR FOR

    (

     

     

    SELECT DISTINCT table_name,

    column_name

     

     

    FROM

    INFORMATION_SCHEMA.columns

     

     

    WHERE column_name IN ('userid', 'created_by', 'last_modified_by'

    )

     

     

    AND (table_name LIKE 'T%' OR table_name LIKE 'L%'

    ))

    DECLARE

     

     

    @Table_Name VARCHAR(255

    )

    DECLARE

     

     

    @Column_Name VARCHAR(255

    )

    DECLARE

     

     

    @Username_Old VARCHAR(10

    )

    DECLARE

     

     

    @Username_New VARCHAR(10

    )

    DECLARE

     

     

    @SQL VARCHAR(4000

    )

    DECLARE

     

     

    @RC

    INT

     

    SET

     

     

    @Username_Old = 'Debs'

    --'XXXXXXXXXX' --

    SET

     

     

    @Username_New = 'DebbieH'

    --'XXXXXXXXXX' --

    OPEN

     

     

    ChangeCursor

    FETCH

     

     

    NEXT FROM ChangeCursor INTO @Table_Name,

    @Column_Name

    SET

     

     

    @SQL = 'Update ' + @Table_name + ' SET ' + @Column_name +

     

     

    ' = ''''' + @Username_New + ''''' WHERE ' + @Column_Name + ' = ''''' +

    @Username_Old

     

    +

    ''''''';'

    WHILE

     

     

    @@FETCH_STATUS =

    0

    BEGIN

     

     

    EXEC ('DECLARE @RC INT;'

    +

     

     

    'SELECT @RC=COUNT(*) FROM ' + @Table_Name + ' WHERE ' + @Column_Name +

     

     

    ' = ''' + @Username_Old + ''';'

    +

     

     

    'IF @RC > 0 PRINT ''UPDATE ' + @Table_Name + ' SET ' + @Column_Name +

     

     

    ' = ''''' + @Username_New + ''''' WHERE ' + @Column_Name + ' = ''''' +

    @Username_Old

     

    + ''''''';'

    )

     

     

    FETCH NEXT FROM ChangeCursor INTO @Table_Name,

    @Column_Name

     

     

    IF @@FETCH_STATUS <> 0

    BREAK

    END

    CLOSE

     

     

    ChangeCursor

    DEALLOCATE

     

     

    ChangeCursor

     

    -----------------------------------------------------

    DECLARE

     

     

    @SQL VARCHAR(4000

    )

    DECLARE

     

     

    @Table_Name VARCHAR(255

    )

    DECLARE

     

     

    @Column_Name VARCHAR(255

    )

    DECLARE

     

     

    @Username_Old VARCHAR(10

    )

    DECLARE

     

     

    @Username_New VARCHAR(10

    )

    SET

     

     

    @Username_Old =

    'Debs'

    SET

     

     

    @Username_New =

    'DebbieH'

    SET

     

     

    @TABLE_NAME =

    'T_METUSER'

    SET

     

     

    @Column_name =

    'userid'

    print

     

     

    'UPDATE ' + @Table_name +

     

     

    ' SET ' + @Column_name +

     

     

    ' = ' + @Username_New +

     

     

    ' WHERE ' + @Column_Name +

     

     

    ' = ' +

    @Username_Old

    PRINT

     

     

    @SQL

     

     

    -------------------------------------------------------------

    USE

     

     

    impresario

    ;

    GO

    DECLARE

     

     

    tables_cursor

    CURSOR

     

     

    FOR

     

     

    SELECT s.name, t.name

     

     

    FROM sys.objects AS

    t

     

     

    JOIN sys.schemas AS s ON s.schema_id = t.

    schema_id

     

     

    WHERE t.type = 'U'

    ;

    OPEN

     

     

    tables_cursor

    ;

    DECLARE

     

     

    @schemaname sysname

    ;

    DECLARE

     

     

    @tablename sysname

    ;

    FETCH

     

     

    NEXT FROM tables_cursor INTO @schemaname, @tablename

    ;

    WHILE

     

     

    (@@FETCH_STATUS <> -1

    )

    BEGIN

     

     

    ;

     

     

    EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;'

    );

     

     

    FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename

    ;

    END

     

     

    ;

    PRINT

     

     

    'The indexes on all tables have been rebuilt.'

    ;

    CLOSE

     

     

    tables_cursor

    ;

    DEALLOCATE

     

     

    tables_cursor

    ;

    GO

Children
No Data