As a SQL Server DBA, you often come across repetitive tasks that can be time-consuming and tedious. One such task is creating users for databases. In this blog post, we will explore how to automate this process using PowerShell and SQL Server Management Objects (SMO).

Before we dive into the script, it’s important to note that the code provided in this example is specific to SQL Server 2016. If you are using a different version, you may need to modify the server name, database name, and the path for the SMO.dll accordingly.

Let’s take a look at the script:

$SqlServer = "localhost"
$SqlDBName = "AdventureWorks2016"

Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer)

# Get all of the current logins and their types
$SqlServer.Logins |
    Select-Object Name, LoginType, Parent

# Create a new login by prompting for new credentials
$NewLoginCredentials = Get-Credential -Message "Enter credentials for the new login"
$NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($SqlServer, $NewLoginCredentials.UserName)
$NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
$NewLogin.Create($NewLoginCredentials.Password)

# Create a new database user for the newly created login
$NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($SqlServer.Databases[$SqlDBName], $NewLoginCredentials.UserName)
$NewUser.Login = $NewLoginCredentials.UserName
$NewUser.Create()
$NewUser.AddToRole("db_datareader")

When you run this script, you will be prompted to enter a username and password for the new login. Once you provide the credentials, a login will be created and a user will be created in the AdventureWorks2016 database with the “db_datareader” role assigned.

It’s important to double-check all the parameters before running the script to avoid any errors. Additionally, keep in mind that this script is just a starting point and can be customized to fit your specific requirements.

Automating tasks like user creation not only saves time but also reduces the chances of human error. By leveraging PowerShell and SMO, you can streamline your workflow and focus on more critical aspects of your job.

There are countless other use cases where PowerShell scripts can be beneficial in SQL Server administration. If you have any interesting scenarios or scripts to share, feel free to leave a comment below.