Changing Other Identifiers

To change user names, login names, device names, remote server names, remote server user names, segment names, and character set and language names, first determine if you can drop the object or user, then add or create it again. If you cannot do that, use the following command to allow direct updates to system tables:

sp_configure "allow updates to system tables", 1

Only a system security officer can set the allow updates to system tables configuration parameter.

Errors during direct updates to system tables can create severe problems in the SAP ASE server. Determine whether you can drop the objects or user, then re-create them:

Identifier Type	Suggested Actions to Avoid Updates to System Tables
User names and login names	To change the name of a user with no objects: Use sp_helprotect `username` in each database to record the user’s permissions. Drop the user from all of the databases (sp_dropuser). Drop the login (drop login). Add the new login name (create login). Add the new user name to the databases (sp_adduser). Restore the user’s permissions with grant.
Device names	If this device is completely allocated, you need not use its name in a create database command, so you can leave the name unchanged.
Remote server names	Unless there are large numbers of remote login names from the remote server, drop the remote server (sp_dropserver) and add it with a new name (sp_addserver).
Remote server logins	Drop the remote login with sp_dropremotelogin, add it with a new name using sp_addremotelogin, and restore the user’s permission to execute procedures with grant.
Segment names	These are rarely used, once objects have been created on the segments.
Character set and language names	Languages and character sets have reserved words as identifiers only if a system administrator has created alternative languages with sp_addlanguage. Drop the language with sp_droplanguage, and add it with a new name.

This table shows possible dependencies on this set of identifiers. See this table for possible dependencies, whether you choose to upgrade by dropping and re-creating objects, by using delimited identifiers, or by performing direct updates to system tables.

Warning! Direct updates to system tables can be very dangerous. You can make mistakes that make it impossible for the SAP ASE server to run or make it impossible to access objects in your databases. Undertake this effort when you are calm and collected, and when little or no production activity is taking place on the server. If possible, use the alternative methods described in the following table.

Considerations When Changing Identifiers
Identifier	Remember To
Login name	Change the user name in each database where this person is a user.
User name	Drop, edit, and re-create all procedures, triggers, and views that use qualified (`owner_name`.`object_name`) references to objects owned by this user. Change all applications and SQL source scripts that use qualified object names to use the new user name. You do not have to drop the objects themselves; `sysusers` is linked to `sysobjects` by the column that stores the user’s ID, not the user’s name.
Device name	Change any SQL source scripts or applications that reference the device name to use the new name.
Remote server name	Change the name on the remote server. If the name that sp_checkreswords reports is the name of the local server, you must restart the server before you can issue or receive remote procedure calls.
Remote server network name	Change the server’s name in the interfaces files.
Remote server login name	Change the name on the remote server.
Segment name	Drop and re-create all procedures that create tables or indexes on the segment name. Change all applications that create objects on segments to use the new segment name.
Character set name	None.
Language name	Change both `master.dbo.syslanguages` and `master.dbo.syslogins`. The update to `syslogins` may involve many rows. Also, change the names of your localization files.

This example shows a “safe” procedure for updating a user name, with all data modification preceded by a begin transaction command. The system security officer executes:

sp_configure "allow updates to system tables", 1

Then you can execute:

begin transaction
update sysusers
set name = "workerbee"
where name = "work"

At this point, run the query, and check to be sure that the command affected only the row that you intended to change. The only identifier change that affects more than one row is changing the language name in syslogins. If the query affected:

Only the correct row – use commit transaction.
More than one row or the incorrect row – use rollback transaction, determine the source of the problem, and execute the command correctly.

When you are finished, the system security officer turns off the allow updates to system tables configuration parameter with this command:

sp_configure "allow updates to system tables", 0

Warning! Only update system tables in a single database in each user defined transaction. Do not issue a begin transaction command and then update tables in several databases. Such actions can make recovery extremely difficult.

The following table shows the system tables and columns that you should update to change reserved words. The tables preceded by “master.dbo.” occur only in the master database. All other tables occur in master and in user databases. Be certain you are using the correct database before you attempt the update. You can check for the current database name with this command:

select db_name()

System Table Columns to Update When Changing Identifiers
Type of identifier	Table to update	Column name
User name	`sysusers`	`name`
Login names	`master.dbo.syslogins`	`name`
Segment names	`syssegments`	`name`
Device name	`sysdevices`	`name`
Remote server name	`sysservers`	`srvname`
Remote server network name	`sysservers`	`srvnetname`
Character set names	`master.dbo.syscharsets`	`name`
Language name	`master.dbo.syslanguages` `master.dbo.syslogins`	`name` `language`