PowerShell – Create a User with Read Rights on a Database

I have written a number of PowerShell script in the blog and they have been used by many. Most of these scripts are there because someone wanted something different and I got about creating something simple. After I create something for a given use case, I tend to share it in the blog so that people can start using the same.

In this scenario, the requirement was to create some sort of script that was to automate some repeatable task. One of the DBA during a consulting engagement said that he had been creating users to databases on a routine manner and wanted to see if this can be scripted easily. Though the whole requirement seemed to be small, I thought to take a look at the SMO objects and create a simple script. I completely understand that there is no one size that fits all. Please note you might need to change the Server Name, Database name and the path for SMO.dll based on the version of SQL Server you are using. In this example, I am using a default instance and I am running SQL Server 2016.

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

As soon as this script is run, you will get a dialog like this:

After you enter a username and password. As per the code, a login is created and a user is created with the db_datareader role assigned to AdventureWorks2016 database. Hence it is important to check all your parameters before running the script and not get any errors.

I always feel there are tons of interesting use cases for you for using scripts. Please let me know some of them and feel free to share them via comments too.

Reference: Pinal Dave (https://blog.sqlauthority.com)