February 14, 2002 at 6:24 am
Greetings,
After having executed the following:
USE Northwind
EXEC sp_addlogin 'me', 'me', 'Northwind'
EXEC sp_grantdbaccess 'me', 'me'
How can I change the login name from 'me' to 'Robert'
Thanks,
Robert
February 14, 2002 at 6:34 am
I think you can just update the name column in sysxlogins in master. Haven't tried. Not sure if you'd have to change anything in the user db or not. Use appropriation caution.
Andy
February 14, 2002 at 7:13 am
There is a name column of each userdb in the sysusers table that may also need to be updated.
February 14, 2002 at 8:34 am
Take a look this will work.
You can effectively rename a user account but as this is messing wit the system tables I would suggest dropping and adding the user back. But for those of you who really want to know how here it is.
First off I tested this and it works fine renaming a user.
However this is directly modifying the system tables and setting the configuration to do this.
I make no guarantees that you could not possible cause yourself an issue.
And if you do make these kinds of changes they are yours to deal with if failure occurrs.
Note: I did find out that you can do this while the user is logged in. They just will have to use the new login name after they logout and come back. Also you will need to be a member of the server admin role to run this.
Ex. usp_RenameLogin 'Test', 'TestLogin'
-----------------------------------Code should be put in master database-----------------------------------------------------
CREATE PROCEDURE usp_RenameLogin
@CurrentLoginsysname,
@NewLoginsysname
AS
DECLARE @SQLState AS VARCHAR(200)
--Configure server to allow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
--Update user login name in master db
SET @SQLState = 'UPDATE master.dbo.sysxlogins SET [name] = ''' + @NewLogin + ''' WHERE [name] = ''' + @CurrentLogin + ''''
EXEC (@SQLState)
--Update user login name in each db where has access as in in sysusers table
SET @SQLState = 'EXEC master.dbo.sp_MSForEachDB ''UPDATE ?.dbo.sysusers SET [name] = ''''' + @NewLogin + ''''' where [name] = ''''' + @CurrentLogin + ''''''''
EXEC (@SQLState)
--Configure server to disallow ad hoc updates to system tables
EXEC master.dbo.sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
GO
February 14, 2002 at 12:41 pm
One thing that would probably need to be tested in a development environment is the effect on objects that are owned by said user. It should not affect any, since those should be keying off UID (except in the case where an owner name other than dbo may have been specified, such as in a stored procedure), but you will want to verify if you do a manual rename such as with Antares' script.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/14/2002 12:42:01 PM
K. Brian Kelley
@kbriankelley
February 15, 2002 at 5:45 am
Just FYI, I test the case of objects owned by a renamed user and here was the results. The owner name changed automatically. If any of the owned objects specify a object owned in the format Owner.Object and the Owner is the one that was renamed these will have to be rebuilt with the correct. Otherwise views, procedures, and tables rolled over fine. Triggers fell into the category of Owner.Object being reference internal and had to be altered.
February 15, 2002 at 9:17 am
That makes sense since the system tables work off smallint for uid in order to join back to sysusers. But always better safe than sorry.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 15, 2002 at 9:26 am
You got that right. I just love people who don't test anything before they use it somewhere they souldn't.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply