批量移植MSSQL2000帐号的方法

批量移植MSSQL2000帐号的方法
在数据库的维护中最怵的就是移植数据库了，尤其是当数据库中有大量的分工详细的账户，笔者就曾遇到一个服务于web的MSSQL2000数据库系统，拥有超过200个登录帐号，与这些帐号相对应的是近百个数据库。

若按照通常移植MSSQL帐号的方法，不仅你要记住每一个帐号的名称、密码，而且你还要记住每个数据库中用户名与每个登录帐号的对应关系，我想这个创建帐号的过程一定会让你抓狂。

本文介绍了一种新的思路，即时是忘记了登录帐号密码的情况下也可以批量移植帐号。

先介绍一下本文中移植帐号的思路及适用范围：
1. 使用bcp将存储于源master数据库中的sysxlogins（存储登录帐号）系统表导出；
2. 将导出的文件复制到目标服务器上，再使用bcp将导出的文件导入到目标数据库中的过渡表中；
3. 开启系统表写入设置，将过渡表中账户数据导入到目的master数据库中的sysxlogi ns中；
4. 将新导入的帐号与数据库中的用户名作映射、对应。

本文中的方法适用于MSSQL2000，无法在MSSQL2005上操作，原因很简单，MSSQL 2005无法更改系统表，且其系统表的名称已经发生变化了。

--第1步是在源数据库中master库的sysxlogins系统表中将帐号信息导出成一个文件c: \logins.dat
exec master..xp_cmdshell 'bcp master..sysxlogins out c:\logins.dat -N -S"(l ocal)" -U"sa" -P"password"'
--第2步是将导出的文件导入到目标服务器中的master..logins过渡表中
if exists(select * from master..sysobjects where type='u' and name='login s')
drop table master..logins
go
--生成logins过渡表的空表结构
SELECT * INTO master..logins FROM master..sysxlogins WHERE 1=2
go
--将导出的帐号文件导入到logins表中
exec master..xp_cmdshell 'bcp master..logins in c:\logins.dat -N -S"(local)" -U"sa" -P"password"'
go
--第3步是将过渡表logins中的信息导入到sysxlogins系统表中
Use Master
go
--开启系统表写入设置
sp_configure 'allow updates', 1
reconfigure with override
go
set ansi_nulls off
go
--将logins过渡表中数据写入到sysxlogins系统表中
insert into
sysxlogins(
srvid,
sid,
xstatus,
xdate1,
xdate2,
name,
password,
dbid,
language)
select
srvid,
sid,
xstatus,
xdate1,
xdate2,
name,
password,
dbid,
language
from sysloginstemp
where name not in (select name from master..sysxlogins) go
set ansi_nulls on
go
Use Master
go
--关闭系统表写入设置
sp_configure 'allow updates', 0
reconfigure with override
go
--删除过渡表
drop table master..logins
--最后一步将数据库中的用户信息与login帐号做对应
/*
这一步主要是使用sp_change_users_login,将dbuser与loginuser映射
sp_change_users_login用来消除孤立用户使用方法：
sp_change_users_login 'Update_One', 'dbUser', 'LoginUser' --将用户dbUser 与帐号LoginUser映射
sp_Msforeachdb用来遍历DBMS下的每个数据库，并执行指定的sql语句。

这个存储过程是非公开的，其用法大家参考：
/features/mssql/article.php/3441031
*/
EXEC sp_MSforeachdb @command1=
/*
定义游标来遍历获取?数据库中的用户名（存放于sysusers）
及其所对应的MSSQL2000登录帐号（存放于syslogins）
*/
'declare user_cur cursor for
select dbname=,loginname=
from ?..sysusers u ,master..syslogins l
where u.sid=l.sid and l.sid<>0x01
declare @dbname varchar(255)
declare @loginname varchar(255)
declare @sql varchar(4000)
open user_cur
fetch next from user_cur into @dbname,@loginname
while @@fetch_status=0
begin
--将?数据库中的用户名与其对应的MSSQL2000帐号建立映射
select @sql=''?..sp_change_users_login ''''Update_One'''',''''''+@ dbname+'''''',''''''+@loginname+''''''''
exec (@sql)
fetch next from user_cur into @dbname,@loginname
end
--关闭游标
close user_cur
deallocate user_cur
'
总结：
本文在移植MSSQL2000账户时使用了一些非常规的方法，可以用来批量移植帐号，降低了移植过程的繁琐程度，虽修改系统表的方法是不值得推荐的，但其中使用的sp_Ms foreachdb、sp_change_users_login方法及思路还是值得大家借鉴的。