Create script for deploying database roles and rights

The script below returns the create statements for all roles in a database. It also creates the statements that add the role members to the roles and assigns the role rights to the objects.

It can be filtered for one or some specific roles, e.g. the role "db_servicecockpit".

select sql from
(
-- 1) create role
select 1 as ord,
r.name as rolename,
concat('CREATE ROLE [',r.name,']') as sql
from sys.database_principals r
union all
-- 2) add members
select 2 as ord,
r.name as rolename,
concat('EXEC sp_AddRoleMember ''',r.name,''', ''',u.name,'''') as sql
from sys.database_principals u
join sys.database_role_members l on l.member_principal_id = u.principal_id
join sys.database_principals r on r.principal_id = l.role_principal_id
union all
-- 3) apply role rights to objects
select 3 as ord,
r.name as rolename,
'GRANT ' + p.permission_name + ' ON ' +
CASE p.class_desc
WHEN 'SCHEMA' THEN concat('[',schema_name(major_id),']')
WHEN 'OBJECT_OR_COLUMN' THEN
CASE
WHEN minor_id = 0 THEN concat('[',OBJECT_SCHEMA_NAME(major_id),'].[',object_name(major_id),']') COLLATE Latin1_General_CI_AS_KS_WS
ELSE
(
SELECT concat(object_name(object_id),' (',c.name,')')
FROM sys.columns c
WHERE o.object_id = p.major_id
AND c.column_id = p.minor_id
)
END
ELSE 'other'
END + ' TO [' + r.name + ']'  as sql
from sys.database_permissions p
join sys.database_principals r on p.grantee_principal_id = r.principal_id
left join sys.objects o on o.object_id = p.major_id
where p.major_id > 0
) x
where rolename = 'db_servicecockpit'
order by ord