迁移用户以及用户对应的角色

我们经常在迁移数据库时，需要将登录账号，用户和用户对应的角色也迁移过去，以下脚本就是迁移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
posted on 2012-10-12 11:46 suther 阅读(318) 评论(0) 收藏举报