Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
160 lines (131 sloc) 7.16 KB
function New-SQLLogin {
<#
.SYNOPSIS
Will create login in sql server. Optionally will add it to specified role.
.DESCRIPTION
It does the following:
1. If no SQL instance is provided it queries server for sql istances and selects if only one exists. If there are more than 1 instance - exists.
2. Checks if login already exists on server. If not - creates one.
3. Checks if provided SQL role is valid. If yes - checks if user is already a member of the role. if not - adds the user to specified sql role.
.PARAMETER ComputerName
SQL server name.
.PARAMETER SQLInstance
SQL Instance name.
.PARAMETER Identity
User to add to SQL Server.
.PARAMETER LoginType
Login type in SQL. Defaults to WindowsUser.
.PARAMETER SQLRole
SQL role to add user to. Defaults to public.
.EXAMPLE
New-SQLLogin -Computername Server1 -Username DOMAIN\user1
It will add windows user DOMAIN\user1 on server Server1
.EXAMPLE
New-SQLLogin -Computername Server1 -SQLInstance 'SQLEXPRESS' -Username DOMAIN\user1
It will add windows user DOMAIN\user1 on server Server1\SQLEXPRESS
.EXAMPLE
New-SQLLogin -Computername Server1 -SQLInstance 'SQLEXPRESS' -Username DOMAIN\user1 -LoginType WindowsUser
It will add windows user DOMAIN\user1 on server Server1\SQLEXPRESS as WindowsUser LoginType
.EXAMPLE
New-SQLLogin -Computername Server1 -SQLInstance 'SQLEXPRESS' -Username DOMAIN\user1 -SQLRole 'sysadmin'
It will add windows user DOMAIN\user1 on server Server1\SQLEXPRESS and add this user sysadmin role.
#>
[CmdletBinding()]
[OutputType([void])]
param(
[Parameter(Mandatory=$true,HelpMessage='Provide SQL Server name', ValueFromPipelineByPropertyName)]
[ValidateScript({ Test-Connection -ComputerName $_ -Quiet -Count 1 })]
[string]
$ComputerName,
[Parameter(Mandatory=$false,HelpMessage='Provide SQL Server instance name. If none - will use default one.')]
[string]
$SQLInstance,
[Parameter(Mandatory=$true,HelpMessage='Provide user to grant permissions to')]
[string]
$Identity,
[Parameter(Mandatory=$false,HelpMessage = 'Provide LoginType')]
[ValidateSet('WindowsGroup', 'WindowsUser')]
[string]
$LoginType = 'WindowsUser',
[Parameter(Mandatory=$false,HelpMessage = 'Provide optional db role. If none - public role will be user')]
[string]
$SQLRole = 'public'
)
begin {
Write-Verbose -Message "Starting $($MyInvocation.MyCommand) "
Write-Verbose -Message 'Execution Metadata:'
Write-Verbose -Message "User = $($env:userdomain)\$($env:USERNAME)"
Write-Verbose -Message "Computername = $env:COMPUTERNAME"
Write-Verbose -Message "Host = $($host.Name)"
Write-Verbose -Message "PSVersion = $($PSVersionTable.PSVersion)"
Write-Verbose -Message "Runtime = $(Get-Date)"
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) BEGIN ] Starting: $($MyInvocation.Mycommand)"
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) BEGIN ] Trying to Load SqlServer.SMO assembly"
try {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
}
catch {
Write-Error -Message "[$((get-date).TimeOfDay.ToString()) BEGIN ] $_ "
}
}
process {
if (-not ($SQLInstance)) {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Enumerating SQL Instances on given server {$Computername}"
$sqlservertemp = new-object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $Computername
$sqlInstEnum = $sqlservertemp | Select-Object -Property InstanceName -ExpandProperty InstanceName
if(-not ($sqlInstEnum)) {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Default instance found on server {$Computername}."
$server = $Computername
$sqlserver = new-object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $server
}
elseif ($sqlInstEnum.Count -ge 2) {
Write-Error -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Found more than 1 instance on server {$Computername}. Please specify which one of following should be used:"
Write-Error -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] SQLInstance: $sqlInstEnum"
break
}
else {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Found instance {$sqlInstEnum} on server {$Computername}"
$server = "$Computername\$sqlInstEnum"
$sqlserver = new-object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $server
}
}
else {
$sqlserver = new-object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList "$Computername\$SQLInstance"
}
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Checking if user {$Identity} exists on server {$sqlserver}"
if($sqlserver.Logins.Contains($Identity) ) {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] User {$Identity} exists on server {$sqlserver}"
}
else {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] User does not exists. Creating user {$Identity} on server {$sqlserver}"
if ($LoginType -eq 'WindowsUser' -or $LoginType -eq 'WindowsGroup') {
$sqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $sqlserver, $Identity
$SqlUser.LoginType = $LoginType
$SqlUser.Create()
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Created user {$Identity}, type {$LoginType} on server {$sqlserver}"
}
}
if($SQLRole -notcontains 'public') {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Checking if role {$SQLRole} on server {$sqlserver} is valid"
$sqlServerRole = $sqlserver.Roles[$SQLRole]
if(-not ($sqlServerRole) ) {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Role {$SQLRole} is not a valid Role on server {$sqlserver}"
break
}
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Role {$SQLRole} is a valid Role on server {$sqlserver}"
if ($sqlServerRole.EnumServerRoleMembers() -contains $Identity) {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] User {$Identity} exists in role {$SQLRole} on server {$sqlserver}"
}
else {
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Adding user {$Identity} to role {$sqlRole} on server {$sqlserver}"
$sqlUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login $sqlserver, $Identity
$sqlServerRole.AddMember("$($sqlUser.Name)")
Write-Verbose -Message "[$((get-date).TimeOfDay.ToString()) PROCESS ] Added user {$Identity} to role {$sqlRole} on server {$sqlserver}"
}
}
}
End{
Write-Verbose "[$((get-date).TimeOfDay.ToString()) END ] Ending: $($MyInvocation.Mycommand)"
Write-Verbose "Ending $($MyInvocation.MyCommand) "
}
}
You can’t perform that action at this time.