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:

Solarwinds

PowerShell - Create a User with Read Rights on a Database powershell-create-user-01

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)

Solarwinds
,
Previous Post
SQL SERVER – Simple Example of BCP Command Line Utility
Next Post
SQL Server – Understanding Connection Timeouts and Query Timeouts

Related Posts

4 Comments. Leave new

  • The following exception occurred while trying to enumerate the collection: “Failed to connect to server VS2005TFS.”.
    At C:\TFS_Health\Health\NHSP\Main\Source\BuildScripts\Untitled8.ps1:10 char:1
    + $SqlServer.Logins | Select-Object Name, LoginType, Parent
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

    Reply
  • Hi Pinal ,

    Im reading your all articles about SQL server its really a good stuf you sharing, actually im looking for some powerrshell script , which can create login and user on SQL Server how that will work you know.

    That automation script has to work like below steps.

    1. when i run the PS file it has to ask SQL login credentials than i will provide my user name and password.
    2. Then it has to ask for SQL server name .
    3. Than it has to ask for SQL Login or windows Login.
    a) if i select sql login it has to ask password.
    b) windows AD account login.
    4. Servers ?
    5. Database rolles
    6. PS file start executed with above information which i provided .
    7. successful message and validation output like it will search new login exist in server and Access roll which is having it now .

    Reply
  • 4th point – Server rolls ?

    Reply

Leave a Reply

Menu