title: database permissions match whoever is running PowerShell script instead of those specified in connection string
body: <p>I want SQL Server permissions to conform to the ID I specify in the connection string, NOT those of whoever happens to be running the PowerShell script (usually me). </p>
  
  <p>For example, I have a table called “MyTable” which I can update but which “TestIDRestricted” cannot.</p>
  
  <p>I log onto the Microsoft SQL Server Management Studio as “TestIDRestricted” and run the following query</p>
  
  <pre><code>select MyField
  from MyTable
  where ID = 2
  </code></pre>
  
  <p>and I see it returns "4".  I now try to update:</p>
  
  <pre><code>update MyTable
  set MyField = 5
  where ID = 2
  </code></pre>
  
  <p>As expected, I get the following failure result:</p>
  
  <pre><code>Msg 229, Level 14, State 5, Line 1
  The UPDATE permission was denied on the object 'MyTable', database 'mydb', schema 'dbo'.
  </code></pre>
  
  <p>Next I run the following UpdateProblem.ps1 PowerShell script:</p>
  
  <pre><code>$ConnectionString = "Server=MyServer\MSSQL2012;Database=mydb;User Id= TestIDRestricted;Password=secretpasswd;"
  $ConnectionString = $ConnectionString + "Trusted_Connection = yes;Persist Security Info=False"
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = $ConnectionString
  $sqlConnection.Open()
  $sqlCommand = $sqlConnection.CreateCommand()
  $updateSQL ="update MyTable set MyField = 5 where ID = 2"
  $sqlCommand.CommandText = $updateSQL 
  write-host $updateSQL
  #following command should fail
  $sqlCommand.ExecuteNonQuery()
  $sqlConnection.close() 
  </code></pre>
  
  <p>I expect this update to fail because I am NOT specifying integrated security in the connection string.  But when I log back onto the Microsoft SQL Server Management Studio as “TestIDRestricted” and again run the following query</p>
  
  <pre><code>select MyField
  from MyTable
  where ID = 2
  </code></pre>
  
  <p>I see it returns "5".  So the update succeeded even though I expect it to fail.  From what I can tell, the reason it succeeded is because the SQL permissions are based on my ID using integrated security instead of the security associated with the ID I use in the connection string.</p>
  
  <p>How can I get the script to use the permissions associated with the ID I specify in the connection string instead of using my ID’s permissions that seem to follow along with integrated security?</p>