,@rolenamevarchar(50)
,@publicrolevarchar(200)
,@countint
DECLAREcur_role CURSOR
LOCAL
STATIC
READ_ONLY
FORWARD_ONLY
FOR
SELECT
dbname
,username
,rolename
,publicrole from#RoleTmep
set@count=0
opencur_role fetchnextfromcur_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')
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
fetchnextfromcur_role into@dbname,@username,@rolename,@publicrole
set@count=@count+1
end
closecur_role
deallocatecur_role
GO
truncatetable#RoleTmep
truncatetabletempdb.dbo.userrole
GO
droptable#RoleTmep
droptabletempdb.dbo.userrole
摘自：飞洋过海blog