How to script SQL database roles using powershell?

Question

Hi everyone.

I am trying to script SQL database roles as the built in SSMS "Task", "Generate scripts" is able to do it. An example of the output can be found below.

I set the following Scripter options:

$scripter.Options.IncludeDatabaseRoleMemberships = $true
$scripter.Options.Permissions = $true

However I only get the CREATE USER... part. Does any one have an example of how to also get the ALTER ROLE statements?

Kind regards
Bob

USE [TestDB]
GO
CREATE USER [MyDBuser] FOR LOGIN [MyDBuser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_executor] ADD MEMBER [MyDBuser]
GO
ALTER ROLE [db_owner] ADD MEMBER [MyDBuser]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [MyDBuser]
GO
ALTER ROLE [db_datareader] ADD MEMBER [MyDBuser]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [MyDBuser]
GO

Answer 1

$scripter.Options.IncludeDatabaseRoleMemberships = $true

That scripts only members of a database role, not the role itself. See DatabaseRole.Script Method

And for the complete SMO documentation see Overview (SMO)