Create a Database User with Read Rights using PowerShell

PowerShell - @SeniorDBA

PowerShell is a powerful tool that can be used for many Database Administrator (DBA) functions, including adding users to your database. In this article by Pinal Dave, we see how easy it is to use PowerShell for this type of typical DBA task.

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.

(You may need to copy and paste the text into your PowerShell editor to see all the text)

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s