June 2, 2003 at 4:54 am
Hi!
I want to ask you guys, how can I rename a user in MS SQL2000 (trusted or sql login)
(the domain user name was renamed and it is too long to restore all permissions, if the user is dropped and recreated)
June 2, 2003 at 10:27 am
That is why the ROLES are for, you can assign all the permissions to the ROLE instead of assigning each and every user, and add the new user or changed users name to that that ROLE. That way you can manage your Sever security easily and effectively.
Shas3
June 2, 2003 at 5:08 pm
I agree that using a role avoids this kind of problem. Definitely something to put on your todo list, or just deal with now and be done with it!
But to answer your question, I don't think renaming users is supported directly (someone tell me if Im wrong!), but what you can do is enable updates to system tables (sp_configure 'allow updates', 1) and then update the name col of the appropriate record in sysxlogins. Definitely run a backup and/or script the logins out in case you make a mistake.
Andy
June 3, 2003 at 2:15 am
I don't think that you can rename a user, but you can use EM to script just the object permissions (deselect 'CREATE' and 'DROP' and select 'object-level permissions') and use the search and replace in Query Analyser to replace your old user's name with the new. Create the new user, run the script and then drop the old user.
My preference is to use roles to avoid this issue. If you are inheriting a database you can use the above technique to also set a role's permissions from a user.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply