use db

鄂州網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián)公司,鄂州網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為鄂州成百上千家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)營(yíng)銷網(wǎng)站建設(shè)要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的鄂州做網(wǎng)站的公司定做！

go

DECLARE 

@Database varchar(255),

@loginName varchar(255),

@roleName varchar(255),

@sql nvarchar(max);

SET @sql=N'';

DECLARE curLogin CURSOR LOCAL for

select db_name() as dbname,dp.name as username,dpr.name as rolename 

from sys.database_principals dp 

join sys.database_role_members drm on drm.member_principal_id=dp.principal_id

join sys.database_principals dpr on drm.role_principal_id=dpr.principal_id 

join sys.server_principals sp on sp.name=dp.name

where 1=1

--and dpr.is_fixed_role=1 

and dp.type<>'R'

and dp.type in('S','U','G') --SQL USER,WINDOWS USER AND windows group

order by username,rolename

OPEN curLogin;

FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql=@sql+N'

use '+@Database+';

if not exists(select * from '+@Database+'.sys.database_principals where name='''+@LoginName+''') 

begin

CREATE USER '+QUOTENAME(@LoginName)+';

end

else 

begin

ALTER USER '+QUOTENAME(@LoginName)+' with login = '+QUOTENAME(@LoginName)+'

end

;

'

--print @sql

--exec sp_executesql @sql

select @sql=@sql+N'

use '+@Database+';

exec sp_addrolemember '''+@roleName+''', ''' + @LoginName + ''''

--exec sp_executesql @sql

FETCH NEXT FROM curLogin INTO @Database,@loginName,@roleName;

END

CLOSE curLogin

DEALLOCATE curLogin

;

--select len(@sql)

--print @sql --this will be truncated 

exec sysadmin.dbo.printmax @sql

go