We often face this in Ballet. I am curious to know how others handle it. When people get married they want us to change their tessitura login name (userid) and when they get separated they want to go back to their original login (part of life). It happened couple of times. For now we are managing it by creating a new login. May be in a future version Tessitura can allow us to change the login name, as long as it is unique, by maintaining a separate primary key to refer & link to different other tables without breaking any referential integrity.
Mo
National Ballet of Canada
We change the ID. It involves creating a new user id and then converting all of their historical activity in the database over to that new id. Here is the script developed by our awesome DBA, Katie Lachance-Duffy:
http://www.tessituranetwork.com/COMMUNITY/members/dantaraborrelli3098/files/Change-userid-for-last-name-changes.sql.aspx
Hi Dan,
Any chance you could re-post that script? The link above is taking me to an error page.
Many thanks!
Mara
I don't know why the file keeps getting removed but here is the actual code:
/****************************************/
/* Step 1: Create new user. */
Create new user & inactivate old user in Tessitura Security module.
/****************************************************************************/
/* Step 2: Change userid in database. */
/* Run the script below in the following databases: */
/* -Impresario */
/* -Impresario_cci */
/* Take the output and run in a separate query for the specified database. */
/* Note that nothing will be changed until you run the output! */
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', 'last_updated_by', 'solicitor','owner','create_by')
AND (table_name LIKE 'T%'
OR (table_name LIKE 'L%'
AND table_name NOT LIKE 'LV%'
)
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 = --Enter old username
SET @Username_New = --Enter new username
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 + ''''''';'
IF @@FETCH_STATUS <> 0 BREAK
END
CLOSE c_Tables
DEALLOCATE c_Tables