MSSQL 如何匯出db所有使用者許可權建立語句

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