SQL DBA with A Beard

He's a SQL DBA and he has a beard

Menu

Skip to content
  • Home
  • Where
  • About Me
    • About Me
    • Twitter
    • LinkedIn
    • Presentations
    • PowerShell Gallery Scripts
    • Script Center Scripts
  • dbatools in a Month of Lunches
  • dbachecks and dbatools
    • dbatools website
    • dbatools Github
    • dbachecks
    • discuss both on Slack
    • dbareports Github

Tag Archives: Account

Creating SQL User and adding to Server Role with PowerShell

Posted on September 21, 2013 by SQLDBAwithTheBeard

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

Subscribe to Blog via Email

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

Join 5,847 other subscribers

dbatools in a Month Of Lunches

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

Join Me for a dbatools Pre-Con in Seattle

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

January 2021
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031
« Sep    

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

  • Setting the default file type for a new file in VS Code
  • Running SQL Queries with Visual Studio Code
  • Export SQL User Permissions to T-SQL script using PowerShell and dbatools
  • List Databases (and Properties) on SQL Server with PowerShell
  • Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users
  • Deploying To a Power Bi Report Server with PowerShell
  • dbachecks - Save the results to a database for historical reporting
  • Converting a Datarow to a JSON object with PowerShell
  • Building Azure SQL Db with Terraform using Azure DevOps
  • Using the new SQLServer Powershell module to get SQL Agent Job Information

Archives

Powered by WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: