use db

鄂州网站建设公司成都创新互联公司,鄂州网站设计制作，有大型网站制作公司丰富经验。已为鄂州成百上千家提供企业网站建设服务。企业网站搭建\外贸营销网站建设要多少钱，请找那个售后服务好的鄂州做网站的公司定做！

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

网站栏目：MSSQL如何导出db所有用户权限创建语句
网址分享：http://scyanting.com/article/poepod.html