我们经常在迁移数据库时,需要将登录账号,用户和用户对应的角色也迁移过去,以下脚本就是迁移SQLServer数据库用户以及用户对应
的角色的脚本;将在Message里面生成对应的脚本。
1 SET NOCOUNT ON 2 GO 3 if exists( SELECT 4 * 5 FROM tempdb.dbo.sysobjects 6 WHERE 7 xtype='U' and name='userrole' 8 9 ) 10 begin 11 drop table tempdb.dbo.userrole 12 CREATE TABLE tempdb.dbo.userrole 13 ( 14 servername varchar(50) 15 ,dbname varchar(100) 16 ,username varchar(100) 17 ,category varchar(100) 18 ,rolename varchar(100) 19 ,publicrole varchar(200) 20 ) 21 end 22 else 23 begin 24 CREATE TABLE tempdb.dbo.userrole 25 ( 26 servername varchar(50) 27 ,dbname varchar(100) 28 ,username varchar(100) 29 ,category varchar(100) 30 ,rolename varchar(100) 31 ,publicrole varchar(200) 32 ) 33 end 34 go 35 EXEC master.dbo.sp_MSforeachdb 'INSERT INTO tempdb.dbo.userrole 36 SELECT 37 @@servername,''?'',b.name AS UserName 38 ,CASE 39 WHEN b.isntgroup=1 THEN ''ntgroup'' 40 WHEN b.isntuser=1 THEN ''ntuser'' 41 WHEN b.issqluser=1 THEN ''sqluser'' 42 WHEN b.isaliased=1 THEN ''aliased'' 43 WHEN b.issqlrole=1 THEN ''sqlrole'' 44 WHEN b.isapprole=1 THEN ''approle'' 45 END AS Category 46 ,c.name AS RoleName,(CASE 47 WHEN EXISTS 48 ( 49 SELECT 1 50 FROM ?.dbo.sysusers 51 WHERE 52 name=b.name 53 ) THEN ''CREATE USER [''+b.name+''] FOR LOGIN [''+b.name+''] '' 54 ELSE '''' 55 END 56 ) as publicrole 57 from ?.dbo.sysmembers a 58 join ?.dbo.sysusers b 59 on a.memberuid=b.uid 60 join ?.dbo.sysusers c 61 on a.groupuid=c.uid 62 where a.memberuid<>1 --and (a.memberuid<16384 or a.memberuid>16393)' 63 64 65 if exists( SELECT 66 * 67 FROM tempdb.dbo.sysobjects 68 WHERE 69 xtype='U' and name='RoleTmep' 70 71 ) 72 drop table temp.dbo.RoleTmep 73 74 SELECT 75 dbname 76 ,username 77 ,rolename 78 ,publicrole into #RoleTmep 79 80 FROM ( 81 SELECT 82 * 83 FROM tempdb.dbo.userrole 84 WHERE 85 username IN ( 86 SELECT 87 name 88 FROM sys.server_principals 89 WHERE 90 is_disabled=0 91 AND type IN('S','U' 92 ) 93 ) --AND rolename <>'RSExecRole' 94 union 95 select @@SERVERNAME,'db', name,'sqluser','sysadmin','' 96 FROM sys.syslogins 97 WHERE 98 sysadmin=1 99 AND isntgroup=0 100 AND name IN ( 101 SELECT 102 name 103 FROM sys.server_principals 104 WHERE 105 is_disabled=0 106 ) 107 108 ) a order by dbname 109 110 SELECT 111 dbname 112 ,username 113 ,rolename from #RoleTmep 114 115 DECLARE @dbname varchar(50) 116 ,@username varchar(50) 117 ,@rolename varchar(50) 118 ,@publicrole varchar(200) 119 ,@count int 120 121 DECLARE cur_role CURSOR 122 LOCAL 123 STATIC 124 READ_ONLY 125 FORWARD_ONLY 126 FOR 127 SELECT 128 dbname 129 ,username 130 ,rolename 131 ,publicrole from #RoleTmep 132 133 134 set @count=0 135 open cur_role fetch next from cur_role into @dbname,@username,@rolename,@publicrole 136 while @@fetch_status = 0 137 begin 138 if(len(@publicrole)>5) 139 begin 140 print('--------Add User:'+@username+' On:'+@dbname+'-----------------') 141 print('USE '+@dbname) 142 print('GO') 143 print('IF NOT EXISTS (SELECT 1 FROM '+@dbname+'.dbo.sysusers WHERE name=N'''+@username+''' ) ') 144 print(@publicrole) 145 print('----------------------------------------------------------------------') 146 print('GO') 147 end 148 149 if(@rolename='sysadmin') 150 begin 151 print('--------Add User:'+@username+' Role:Sysadmin'+'-----------------') 152 print('EXEC master..sp_addsrvrolemember @loginame = N'''+@username+''', @rolename = N''sysadmin''') 153 print('----------------------------------------------------------------------') 154 print('GO') 155 end 156 else 157 begin 158 print('---------Add User:'+@username+'Role:'+@rolename+' On '+@dbname+'----------') 159 print('USE '+@dbname) 160 print('GO') 161 print('EXEC sp_addrolemember N'''+@rolename+''', N'''+@username+'''') 162 print('----------------------------------------------------------------------') 163 print('GO') 164 end 165 fetch next from cur_role into @dbname,@username,@rolename,@publicrole 166 set @count=@count+1 167 end 168 close cur_role 169 deallocate cur_role 170 GO 171 truncate table #RoleTmep 172 truncate table tempdb.dbo.userrole 173 GO 174 drop table #RoleTmep 175 drop table tempdb.dbo.userrole