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).
For example, I have a table called “MyTable” which I can update but which “TestIDRestricted” cannot.
I log onto the Microsoft SQL Server Management Studio as “TestIDRestricted” and run the following query
select MyField
from MyTable
where ID = 2
and I see it returns "4". I now try to update:
update MyTable
set MyField = 5
where ID = 2
As expected, I get the following failure result:
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'MyTable', database 'mydb', schema 'dbo'.
Next I run the following UpdateProblem.ps1 PowerShell script:
$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()
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
select MyField
from MyTable
where ID = 2
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.
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?
You're setting Trusted_Connection to TRUE; this almost certainly means you're connecting with the credentials of the current user, not those specified in the connection string. Try:
$ConnectionString = "Server=MyServer\MSSQL2012;Database=mydb;User Id= TestIDRestricted;Password=secretpasswd;"
$ConnectionString = $ConnectionString + "Trusted_Connection = no;Persist Security Info=False"