Create A Sql Server User Using Powershell

I've written a powershell script that creates a new sql server database and login, and then sets the database owner to the newly created user. This is successful. However, I get a

Solution 1:

This isn't an answer to the problem I was encountering, just a work around. The script is being run as part of an automated deployment, the overall scripts are run under the "NT AUTHORITY\SYSTEM" username, so to get around my logging in issue I'm simply using Integrated Security=true.

Solution 2:

I think your final line should read:

Invoke-Sqlcmd -ServerInstance '(local)' -Database 'TestDB' -Username 'TestUser' -Password 'Password1' -Query "SELECT * FROM sysusers"

Notice the use of '(local)' rather than 'localhost'.

Solution 3:

follow the codes below

$SqlServer = "servar.site.com Or server ip with port"$SqlDBName = "dbName"$sqlConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$sqlConnection.ServerInstance=$SqlServer$sqlConnection.LoginSecure = $false$sqlConnection.Login = "userid if you have"$sqlConnection.Password = "password if is needed to connect to sql server"

Add-Type -Path "C:\Program Files\Microsoft SQL 
Server\140\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"$server = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)

 # get all of the current logins and their types$server.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($server, 
$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($server.Databases[$SqlDBName], 
$NewLoginCredentials.UserName)
$NewUser.Login = $NewLoginCredentials.UserName
$NewUser.Create()
$NewUser.AddToRole("db_datareader")