How to Script SQL database role permissions and securables

Question : How can I script database role permissions?  I’ve created the database role but need to migrate the database role  permissions.

Answer: Use this DBA script on the source database . Add the database role name as a value to the @DBRoleName  variable.Copy and paste the GRANT EXECUTE statements and execute them on the target database

 Script database role permissions

SET NOCOUNT ON

Use mydb;  -- ADJUST DB NAME

DECLARE @RoleName varchar(50) = 'my_role' --- PLACE ROLE NAME

SELECT 'CREATE ROLE ' + @RoleName + ';'+ char(13)

SELECT  'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO [' + rol.name + '] ;' + char(13) COLLATE Latin1_General_CI_AS

from sys.database_permissions prm join sys.database_principals rol on prm.grantee_principal_id = rol.principal_id

where rol.name = @RoleName

Read More on database roles

SQL Server database roles and GDPR

SQL Database roles and members

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA