Changing Sybase user login name
Mark Budka
a Sun. He is using it to change the dataserver login name of a user.
He has tested it on his development database and it seems to run just
fine. He is wary of installing it on a production machine however,
because it seems like such a useful function and he can't imagine why
Sybase doesn't make it readily available. Does anyone out there know of
any reason why this might be a BAD thing?
If not, then feel free to use this code yourself. It has been tested
but not blessed, so of course you're using it at your own risk. Thanks
in advance.
Please respond via mail to ...llyene.jpl.nasa.gov!seila!scott
He will also mail summaries to anyone who requests such.
<<<<<< Attached TEXT file follows >>>>>>
/* Generated by "m4 sp_renamelogin | isql" */
/*
Desc : Change the login name of a user from oldname to newname.
(This is an update to the master..syslogins table)
Outputs : None but diagnostic PRINT statements.
Returns : 0 Successful completion
-1 Error occurred
Comments : If this procedure is defined in the master database it
will be callable from any user database.
*/
/* This is the code for the allow updates config parameter in SYBASE. */
define( `CONFIG_ALLOW_UPDATES', 102 )
/* SQL to enable system updates */
define( `SET_ALLOW_ON',UPDATE master..sysconfigures SET value = 1 WHERE config
= CONFIG_ALLOW_UPDATES)
/* SQL to disable system updates */
define( `SET_ALLOW_OFF',UPDATE master..sysconfigures SET value = 0 WHERE config
= CONFIG_ALLOW_UPDATES)
/* ===========================================================================
*/
IF ( EXISTS ( SELECT * FROM sysobjects WHERE name = 'sp_renamelogin' AND type
= 'P' ) )
DROP PROCEDURE sp_renamelogin
go
/* @(#)sp_renamelogin 1.2 2/5/93 13:26:48 */
/* ===========================================================================
*/
CREATE PROCEDURE sp_renamelogin
@oldname varchar(32),
@newname varchar(32)
AS
BEGIN /* sp_renamelogin */
DECLARE @i int
DECLARE @initConfigValue int
DECLARE @msg varchar(255)
/* 0. Verify we are SA. */
IF ( SUSER_ID() != 1 ) BEGIN
PRINT "Msg: Only SA can run this procedure"
RETURN -1
END
/* 1. Verify that oldname exists as login. */
SELECT @i = COUNT(*) FROM master..syslogins WHERE name = @oldname
IF ( @i = 0 ) BEGIN
SELECT @msg = "Msg: Can't find login '"+@oldname+"'."
PRINT @msg
RETURN -1
END
/* 2. Verify that newname doesn't exist as login. */
SELECT @i = COUNT(*) FROM master..syslogins WHERE name = @newname
IF ( @i != 0 ) BEGIN
SELECT @msg = "Msg: There is already a login with the name '"+@newname+"'."
PRINT @msg
RETURN -1
END
/* 3. Get current state of 'ALLOW UPDATES' config value. */
SELECT @initConfigValue = value
FROM master..syscurconfigs
WHERE config = CONFIG_ALLOW_UPDATES
IF ( @@rowcount != 1 ) BEGIN
PRINT "Msg: Can't find current ALLOW UPDATES value."
RETURN -1
END
/* 4. If the allow updates is turned off, turn it on. */
IF ( @initConfigValue = 0 ) BEGIN
/* 4.1 Set the configuration table to allow updates */
SET_ALLOW_ON
IF ( @@rowcount = 0 ) BEGIN
PRINT "Msg: UPDATE of sysconfigures to allow updates failed."
RETURN -1
END
/* 4.2 Run RECONFIGURE WITH OVERRIDE to put into syscurconfigs. */
RECONFIGURE WITH OVERRIDE
IF ( @@error != 0 ) BEGIN
PRINT "Msg: RECONFIGURE WITH OVERRIDE failed."
/* Reset sysconfigures or we will be in ALLOW updates when server restarts.
*/
SET_ALLOW_OFF
IF ( @@rowcount = 0 ) BEGIN
PRINT "Msg: UPDATE of sysconfigures to reset allow updates failed."
END
RETURN -1
END
END
/* 5. Update the logins table to change name. */
UPDATE master..syslogins
SET name = @newname
WHERE name = @oldname
/* If we for some reason didn't update the table, print message but don't
return. */
IF ( @@rowcount = 0 ) PRINT "Msg: UPDATE on syslogins failed."
/* 6. If the config value was initially OFF, turn it back off now. */
IF ( @initConfigValue = 0 ) BEGIN
SET_ALLOW_OFF
IF ( @@rowcount = 0 ) BEGIN
PRINT "Msg: UPDATE of sysconfigures to initial value of allow updates
failed."
RETURN -1
END
RECONFIGURE WITH OVERRIDE
IF ( @@error != 0 ) BEGIN
PRINT "Msg: 2nd RECONFIGURE WITH OVERRIDE failed."
RETURN -1
END
END
SELECT @msg = "Login '"+@oldname+"' successfully renamed to '"+@newname+"'."
PRINT @msg
RETURN 0
END /* sp_renamelogin */
go
/* ===========================================================================
*/
go
/* This code is for use with SQL*Debug and/or dbgp command */
go
/* End of sp_renamelogin */
Mark Parsons
. . . . . code for updating master..syslogins deleted . . . .
Looks like you put in all the necessary checks. But all the checking
for the 'allow updates' value . . .and changing it . . . isn't necessary
if you load your procedure while 'allow updates' is set to ON.
According to the manuals, and what I've been able to do, you can do the
following and not have to worry about the value of the 'allow updates'
configuration option:
turn 'allow updates' on
load your sp
turn 'allow updates' off
Your sp will take on the value of 'allow updates' when you load it.
Subsequent changes to the 'allow updates' option will not affect
the sp.
This is how the Sybase pre-defined sp's work. They were created
with 'allow updates' turned on. When they turned 'allow updates'
off, the sp's still think 'allow updates' is turned on. A lot of
your Sybase pre-defined sp's do modifications to system tables without
having to check or set the 'allow updates' option.
Did that make sense?
Mark