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 haven't had a case of separation yet, but when people get married, a couple of people have asked to change their userid and we've just been up front and said we can't. In my experience nobody's been terribly upset by getting a "no".
We change their email addresses, and generally that's the bigger concern: that communication with the outside world sees their new names. We do change the lname, so any report (or Tstats) that displays the actual name tied to the userid shows their full current names, but the userids stays the same.
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
Thanks Amanda and Dan for your response, and the awesome script! This will give us some food for thought.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Dan Taraborrelli Sent: Friday, October 28, 2011 2:56 PM To: Mohiuddin Faruqe Subject: Re: [Tessitura Technical Forum] A Practical Issue
We change the name. 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://dl.dropbox.com/u/414718/Change%20userid%20for%20last%20name%20changes.sql
From: Mohiuddin Faruqe <bounce-mohiuddinfaruqe8297@tessituranetwork.com> Sent: 10/28/2011 12:10:30 PM
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
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
Thank you, Dan!
Mara Hazzard-Wallingford
Director, Yale Tessitura Consortium
203.432.8822
This email was typed using an eco-friendly font, Spranq eco sans.
If you would like to install this font, click here.
P Before printing, please think about the environment