December 12, 2007 at 8:15 am
Hi!
I need to either rename or copy a group of logins/database users.
All login names used a certain naming convention with three initial letters. I need to change the first three.
You can't rename a login in SQL Server 2000? Could you copy/transfer roles and permissions from one login to another?
December 12, 2007 at 8:36 am
ALTER LOGIN should allow you to specify a new name.
With a script, you could drive this for a group of logins. Permissions should be the same.
ALTER LOGIN STEVE
NAME = 'STEVEJ'
December 12, 2007 at 8:45 am
Isn't that just for SQL Server 2005?
December 12, 2007 at 9:19 am
Ay, sorry. That is a 2005 thing. For some reason I thought I was in that forum. Sorry 🙁
This works, be very, very careful
sp_configure 'allow updates', 1
go
reconfigure with override
go
update sysxlogins
set name = 'BillG'
where name = 'Bill'
sp_configure 'allow updates', 0
go
reconfigure with override
go
That's if these are SQL logins. If they are Windows, then not sure and slightly scared to check here. Might try a test of that on a server.
December 12, 2007 at 9:43 am
Thanks
I think I'll do it the hard way though, with a cursor and creating new logins, adding roles and dropping the old ones.
Don't want to update the systemtables.
December 12, 2007 at 9:54 am
I'd definitely create new roles that manage the permissions and then be sure you have each login in the correct role that matches permissions from the previous ones.
Sorry I don't have a better solution. You could Generate scripts (logins, users/permissions) and then Search and replace the names. That might be easier, but you have to "trust" the scripting is correct and you haven't missed anything.
If you come up with an interesting script, feel free to submit it here or write us an article. I'm sure others could benefit.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply