question

Bob1971-8663 avatar image
0 Votes"
Bob1971-8663 asked OlafHelper-2800 answered

How to script SQL database roles using powershell?

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





sql-server-generalwindows-server-powershell
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm assuming one of the *role commands is what you're after.

https://dbatools.io/commands/

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

$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)



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.