|
|
| |
Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for langauges that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description. | You can effectively rename a user account but as this is messing with 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.
Thru testing the following results occurr:
In the case of objects owned by a renamed user. The owner name changes automatically. If any of the owned objects specify an owned object (in the format Owner.Object) and the Owner is the one that was renamed these will have to be rebuilt/altered with the correct owner replacements. Otherwise views, procedures, and tables roll over fine. Triggers fell into the category of Owner.Object being reference internal and had to be altered.
Ex. usp_RenameLogin 'Test', 'TestLogin'
---Code should be put in master database
---
CREATE PROCEDURE usp_RenameLogin
@CurrentLogin sysname,
@NewLogin sysname
AS
DECLARE @SQLState AS VARCHAR(200)
--Configure server to allow ad hoc updat
-- es 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 wher
-- e 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 | |
Other 19 submission(s) by this author
|
|
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
There are no comments on this submission.
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
|
|
