SQL DBA with A Beard

He's a SQL DBA and he has a beard

Menu

Skip to content
  • Home
  • About Me
  • dbatools in a Month of Lunches

Daily Archives: September 21, 2013

Creating SQL User and adding to Server Role with PowerShell

Posted on September 21, 2013 by RobSewell

Another post in the PowerShell Box of Tricks series.

In yesterdays post Creating a Windows User and Adding to SQL Role we created a Windows User, today it’s a SQL User. Again it is nice and simple and allows you to pipe input from other sources enabling you to easily and quickly repeat any process that needs SQL Users.

It is pretty similar as you would expect. We create a Login Object, set the Logintype to  SqlLogin add the Password and create it with the Create Method. It is then added to the Role Specified

image

The same error checking is performed as the Windows Login function. If the login already exists on the server it will just add it to the role and if the role has been mistyped it will let you know. It does this by checking if the role object is Null for the Roles and the Contains Method for the Logins

image

The function is called as follows.

Add-SQLAccountToSQLRole FADE2BLACK Test Password01 dbcreator

The code can be found here

########################################################################
#
# NAME: Add-SQLAccountToSQLRole.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:11/09/2013
#
# COMMENTS: Load function to create a sql user and add them to a server role
#
# USAGE: Add-SQLAccountToSQLRole FADE2BLACK Test Password01 dbcreator
#        Add-SQLAccountToSQLRole FADE2BLACK Test Password01 public

Function Add-SQLAccountToSQLRole ([String]$Server, [String] $User, [String]$Password, [String]$Role) {

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

    # Check if Role entered Correctly
    $SVRRole = $svr.Roles[$Role]
    if ($SVRRole -eq $null) {
        Write-Host " $Role is not a valid Role on $Server"
    }

    else {
        #Check if User already exists
        if ($svr.Logins.Contains($User)) {
            $SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $Server, $User
            $LoginName = $SQLUser.Name
            if ($Role -notcontains "public") {                   
                $SVRRole.AddMember($LoginName)
            }
        }
        else {
            $SqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $Server, $User
            $SqlUser.LoginType = 'SqlLogin'
            $SqlUser.PasswordExpirationEnabled = $false
            $SqlUser.Create($Password)
            $LoginName = $SQLUser.Name
            if ($Role -notcontains "public") {
                $SVRRole.AddMember($LoginName)
            }
        }
    }
}

 

Share this:

  • Email
  • Facebook
  • Twitter
  • LinkedIn
  • Print
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
  • WhatsApp
  • Skype

Like this:

Like Loading...
Posted in PowerShell, Powershell Box of Tricks, SQL Server / Tagged Account, automate, Box of Tricks, Powershell, Roles, SQL, SQL User / Leave a comment

This blog has moved
blog.robsewell.com

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,861 other subscribers

dbatools in a Month Of Lunches

50% off the book until September 8!
Use code mlsewell at manning.com

Follow me on Twitter

My Tweets

Recent Posts

  • #tsql2sday #130 – Automate your stress away – Getting more SSIS Agent Job information
  • Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools
  • Notifying a Teams Channel of a SQL Agent Job result
  • Sending a SQL Agent Job results overview to a Microsoft Teams Channel
  • Using Secret Management module to run SSMS, VS Code and Azure Data Studio as another user

Calendar

September 2013
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Aug   Oct »

Recent Posts

  • #tsql2sday #130 – Automate your stress away – Getting more SSIS Agent Job information
  • Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools
  • Notifying a Teams Channel of a SQL Agent Job result
  • Sending a SQL Agent Job results overview to a Microsoft Teams Channel
  • Using Secret Management module to run SSMS, VS Code and Azure Data Studio as another user

Menu

  • Twitter
  • LinkedIn

Top Posts & Pages

  • PowerShell, Pester and Ola Hallengrens Maintenance Solution
  • dbatools with SQL on Docker and running SQL queries
  • Using the new SQLServer Powershell module to get SQL Agent Job Information
  • Pester Test Inception and the Show Parameter
  • VS Code - Automatic Dynamic PowerShell Help
  • #tsql2sday #130 - Automate your stress away - Getting more SSIS Agent Job information
  • Automatically updating the version number in a PowerShell Module - How I do regex

Archives

Powered by WordPress.com.
 

Loading Comments...
 

    %d bloggers like this: