How to change a user's name in SQL Server 2000?

Question

I'm not asking how to change the user's login, which is doable with sp_change_users_login, but really the user's name (which comes under the Name column of the list of users in Enterprise Manager).

Is there a stored procedure to do it? Some other command?

Answer 1

In SQL Server 2000, I think you'll have to drop and re-create the user, unless you want to reply on crazy hacks like this one:

• Rename User Login

I don't really recommend that, both because updating system catalogs is dangerous, and also because it uses undocumented, unsupported, and buggy sp_MSForEachDB (see why I urge you to stay away from that one).

In versions newer than SQL Server 2000, it is as simple as:

ALTER USER foo WITH NAME = bar;