学步园

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

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

SET NOCOUNT ON
GO
if exists( SELECT 
               * 
           FROM tempdb.dbo.sysobjects 
           WHERE 
               xtype='U' and name='userrole'
        
        ) 
begin
drop table tempdb.dbo.userrole 
CREATE TABLE tempdb.dbo.userrole 
(
     servername varchar(50)
    ,dbname varchar(100)
    ,username varchar(100)
    ,category varchar(100)
    ,rolename varchar(100)
    ,publicrole varchar(200)
)
end
else
begin
 CREATE TABLE tempdb.dbo.userrole 
 (
      servername varchar(50)
     ,dbname varchar(100)
     ,username varchar(100)
     ,category varchar(100)
     ,rolename varchar(100)
     ,publicrole varchar(200)
 )
end
go
EXEC master.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 
    on a.memberuid=b.uid
    join ?.dbo.sysusers c
    on a.groupuid=c.uid
    where  a.memberuid<>1 --and  (a.memberuid<16384 or a.memberuid>16393)'


if exists( SELECT 
               * 
           FROM tempdb.dbo.sysobjects 
           WHERE 
               xtype='U' and name='RoleTmep'
        
        ) 
drop table temp.dbo.RoleTmep

SELECT 
    dbname
   ,username
   ,rolename
   ,publicrole  into #RoleTmep

FROM ( 
        SELECT 
            * 
        FROM tempdb.dbo.userrole 
        WHERE 
            username  IN (
                    SELECT 
                        name 
                    FROM sys.server_principals 
                    WHERE 
                        is_disabled=0 
                        AND type IN('S','U'
                )
     ) --AND rolename <>'RSExecRole'
union 
select @@SERVERNAME,'db', name,'sqluser','sysadmin','' 
FROM sys.syslogins 
WHERE 
    sysadmin=1 
    AND isntgroup=0 
    AND name IN (
            SELECT 
                name 
            FROM sys.server_principals 
            WHERE 
                is_disabled=0
        )

) a order by username

SELECT 
    dbname
   ,username
   ,rolename from #RoleTmep

DECLARE @dbname varchar(50)
       ,@username varchar(50)
       ,@rolename varchar(50)
       ,@publicrole varchar(200)
       ,@count int

DECLARE cur_role CURSOR 
    LOCAL 
    STATIC 
    READ_ONLY  
    FORWARD_ONLY 
FOR 
SELECT 
    dbname
   ,username
   ,rolename
   ,publicrole from #RoleTmep

set @count=0
open cur_role fetch next from cur_role into @dbname,@username,@rolename,@publicrole
 while @@fetch_status = 0 
begin
 if(len(@publicrole)>5)
  begin
    print('--------Add User:'+@username+' On:'+@dbname+'-----------------')
    print('USE '+@dbname)
    print('GO')
    print('IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.sysusers WHERE name=N'''+@username+''' ) ')
    print(@publicrole)
    print('----------------------------------------------------------------------')
    print('GO')
  end

if(@rolename='sysadmin')
  begin
   print('--------Add User:'+@username+' Role:Sysadmin'+'-----------------')
   print('EXEC master..sp_addsrvrolemember @loginame = N'''+@username+''', @rolename = N''sysadmin''')
   print('----------------------------------------------------------------------')
   print('GO')
  end
else
   begin
     print('---------Add User:'+@username+'Role:'+@rolename+' On '+@dbname+'----------')
     print('USE '+@dbname)
     print('GO')
     print('EXEC sp_addrolemember N'''+@rolename+''', N'''+@username+'''')
     print('----------------------------------------------------------------------')
     print('GO')
   end
fetch next from cur_role into @dbname,@username,@rolename,@publicrole
set @count=@count+1  
end
close cur_role
deallocate cur_role
GO
truncate table #RoleTmep
truncate table tempdb.dbo.userrole 
GO
drop table #RoleTmep
drop table tempdb.dbo.userrole