Change User ID

Hey Kris,

If you're SQL savvy or have a DBA at your disposal, I created an SQL script for doing this.  We had a few users who got married and wanted to change their name, and we wanted to keep all their actions in Tessitura tied to their new account.  It searches the database for any table with the standard username columns and generates UPDATE statements to make the username changes (I did it this way so I could test the update statements manually in our test database first, rather than just having it auto-change everything.)  The script is below.  Definitely test this in a non-production database first!  So far it's worked pretty good for us, however I've not run this since we've upgraded to Tessitura 9.

Kevin.

DECLARE c_Tables 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 = 'oldusername'

SET @Username_New = 'newusername'

OPEN c_Tables

FETCH NEXT FROM c_Tables INTO @Table_Name, @Column_Name

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 c_Tables INTO @Table_Name, @Column_Name

      IF @@FETCH_STATUS <> 0 BREAK

END

CLOSE c_Tables

DEALLOCATE c_Tables

Hey Kris,

If you're SQL savvy or have a DBA at your disposal, I created an SQL script for doing this.  We had a few users who got married and wanted to change their name, and we wanted to keep all their actions in Tessitura tied to their new account.  It searches the database for any table with the standard username columns and generates UPDATE statements to make the username changes (I did it this way so I could test the update statements manually in our test database first, rather than just having it auto-change everything.)  The script is below.  Definitely test this in a non-production database first!  So far it's worked pretty good for us, however I've not run this since we've upgraded to Tessitura 9.

Kevin.

DECLARE c_Tables 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 = 'oldusername'

SET @Username_New = 'newusername'

OPEN c_Tables

FETCH NEXT FROM c_Tables INTO @Table_Name, @Column_Name

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 c_Tables INTO @Table_Name, @Column_Name

      IF @@FETCH_STATUS <> 0 BREAK

END

CLOSE c_Tables

DEALLOCATE c_Tables

Thanks, Kevin! I'll give it a try...

-Kris