dbatools v1.0? It’s available – Check it out!

Dear reader, before continue please open a PowerShell console and run the following command:

 
Install-Module -Name dbatools

If you are doing this on the date of this blog post, you have just installed dbatools v1.0!

After more than 200 commits, the work of more than 20 contributors and 20 days since the last published version, dbatools v1.0 is live!

To all of you that have contributed direct or indirectly to the module a big thank you!

Fun fact

I’m sure this was one of the longest periods without releasing new versions since we started doing it more often.

Bare minimum has been 1 release per week

But, there are good reasons for it! v1.0 brings

– Standardised code – parameters names / output

– Code cleanup

– More tests

– Azure connections supported

– And of course, fixes and new (13) commands.

You can read the v1.0 change log for more details.

New commands

From the 13 new commands, I decided to share the ones that make it possible to interact with Server/Database roles.

Here is the list of the newest commands:

– Add-DbaDbRoleMember

– Get-DbaDbRole

– New-DbaDbRole

– New-DbaInstanceRole

– Remove-DbaDbRole

– Remove-DbaDbRoleMember

– Remove-DbaInstanceRole

Note: Database Application Roles are not covered yet.

Note2: A new command to add logins to one (or more) server role is being cooked.

This is why we release often, improvements and new features are always on our pipeline.

Code examples

Here is a short script that shows how you can leverage these new commands.

Don’t forget to use Get-Help or visit our docs page to know more about the commands.

$instance = "sql2016"
$login = "domainuser"
$newServerRole = "securityMaster"
$defaultExistingServerRole = "sysadmin"
$database = "db1"
$username = $login
$newDatabaseRole = "SPExecutor"
### Create
# Create new login and set default database
New-DbaLogin -SqlInstance $instance -Login $login -DefaultDatabase $database
# Create new server role
New-DbaInstanceRole -SqlInstance $instance -ServerRole $newServerRole
# Create new database user
New-DbaDbUser -SqlInstance $instance -Database $database -Username $username -Login $login
# Create new database role
New-DbaDbRole -SqlInstance $instance -Database $database -Role $newDatabaseRole
# Add new user to the newly created database role
Add-DbaDbRoleMember -SqlInstance $instance -Database $database -User $username -Role $newDatabaseRole

### Now using Get-Dba*Role*
# Get all members of an role (or list of roles)
Get-DbaInstanceRoleMember -SqlInstance $instance -ServerRole $defaultExistingServerRole | Format-Table -AutoSize

# Get newly create server role 'securityMaster' and defaul existing role 'sysadmin'
Get-DbaInstanceRole -SqlInstance $instance -ServerRole $newServerRole, $defaultExistingServerRole

### Database level
# Get newly creted 'SPExecuter' role
Get-DbaDbRole -SqlInstance $instance -Database $database -Role $newDatabaseRole
# Get all users member of an role (or list of roles)
Get-DbaDbRoleMember -SqlInstance $instance -Database $database -Role $newDatabaseRole

### Clean up
# Remove user from database role
Remove-DbaDbRoleMember -SqlInstance $instance -Database $database -Role $newDatabaseRole -User $username
# Remove role from database
Remove-DbaDbRole -SqlInstance $instance -Database $database -Role $newDatabaseRole
# Remove server role from instance
Remove-DbaInstanceRole -SqlInstance $instance -ServerRole $newServerRole

Hope you found them as useful as we did!