迁移用户以及用户对应的角色 (一)

我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移SQLServer数据库用户以及用户对应

的角色的脚本；将在Message里面生成对应的脚本。

SETNOCOUNT ON
GO
ifexists( SELECT
*
FROMtempdb.dbo.sysobjects
WHERE
xtype='U'andname='userrole'

)
begin
droptabletempdb.dbo.userrole
CREATETABLEtempdb.dbo.userrole
(
servername varchar(50)
,dbname varchar(100)
,username varchar(100)
,category varchar(100)
,rolename varchar(100)
,publicrole varchar(200)
)
end
else
begin
CREATETABLEtempdb.dbo.userrole
(
servername varchar(50)
,dbname varchar(100)
,username varchar(100)
,category varchar(100)
,rolename varchar(100)
,publicrole varchar(200)
)
end
go
EXECmaster.dbo.sp_MSforeachdb 'INSERT INTO tempdb.dbo.userrole
SELECT
@@servername,'' '',b.name AS UserName
,CASE
WHEN b.isntgroup=1 THEN ''ntgroup''
WHEN b.isntuser=1 THEN ''ntuser''
WHEN b.issqluser=1 THEN ''sqluser''
WHEN b.isaliased=1 THEN ''aliased''
WHEN b.issqlrole=1 THEN ''sqlrole''
WHEN b.isapprole=1 THEN ''approle''
END AS Category
,c.name AS RoleName,(CASE
WHEN EXISTS
(
SELECT 1
FROM .dbo.sysusers
WHERE
name=b.name
) THEN ''CREATE USER [''+b.name+''] FOR LOGIN [''+b.name+''] ''
ELSE ''''
END
) as publicrole
from .dbo.sysmembers a
join .dbo.sysusers b