TSQL To assign DB ROLE BULK of particular databases

TSQL To assign DB ROLE BULK of particular databases 

  --SELECT * FROM dbo.dbRolesUsersMap ('db_role_name')

 --SELECT * FROM dbo.dbRolesUsersMap (DEFAULT)

use db_name

    Declare @CustomeRole varchar(8000)

    Declare @provideuserrolerole varchar(8000)

Declare @user varchar(30)

Declare @assignrole varchar(100)

Declare @i int

    Declare @j int

set @assignrole='db_role_name'

  Select Distinct Rno=ROW_NUMBER()over(Order by members.name), members.name as 'members_name'

  into ##providedbprivilgeobjects 

FROM sys.database_role_members rolemem

INNER JOIN sys.database_principals roles

ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members

ON rolemem.member_principal_id = members.principal_id

where roles.name not in (SELECT Login_Name FROM dbo.dbRolesUsersMap ('db_role_name'))

and members.name not like '%\%'

ORDER BY members.name

Select @i=1,@j=COUNT(*) from ##providedbprivilgeobjects    

  

While @i <= @j    

Begin    

 

 Select @user=members_name from ##providedbprivilgeobjects where Rno=@i  

  set @provideuserrolerole='Use db_name'+space(2)+'ALTER ROLE '+space(1)+'['+@assignrole+']'+space(2)+'ADD MEMBER '+space(2)+'['+@user+']'+''

  

  --select @provideuserrolerole

  Print @provideuserrolerole

    --Execute(@provideuserrolerole) 

SET @i=@i+1

 End

 drop table ##providedbprivilgeobjects

Note:

use db_name

CREATE FUNCTION dbo.dbRolesUsersMap (@dbRole SYSNAME = '%')
RETURNS TABLE
AS
RETURN (
      SELECT 
        User_Type = 
           CASE mmbrp.[type] 
           WHEN 'G' THEN 'Windows Group' 
           WHEN 'S' THEN 'SQL User' 
           WHEN 'U' THEN 'Windows User' 
           END,
         Database_User_Name = mmbrp.[name],
         Login_Name = ul.[name],
         DB_Role = rolp.[name]
      FROM sys.database_role_members mmbr, -- The Role OR members associations table
         sys.database_principals rolp,     -- The DB Roles names table
         sys.database_principals mmbrp,    -- The Role members table (database users)
         sys.server_principals ul          -- The Login accounts table
      WHERE Upper (mmbrp.[type]) IN ( 'S', 'U', 'G' )
         -- No need for these system account types
         AND Upper (mmbrp.[name]) NOT IN ('SYS','INFORMATION_SCHEMA')
         AND rolp.[principal_id] = mmbr.[role_principal_id]
         AND mmbrp.[principal_id] = mmbr.[member_principal_id]
         AND ul.[sid] = mmbrp.[sid]
         AND rolp.[name] LIKE '%' + @dbRole + '%'
      )
GO

  --SELECT * FROM dbo.dbRolesUsersMap ('db_role_name')
 --SELECT * FROM dbo.dbRolesUsersMap (DEFAULT)