User with DBO permission can't remotely connect

have brand new 2012 express and am using a script to out put data to a table, but when the script runs i get an access error, but I'm listed as a dbo under the database.. not sure what could be preventing

Exception calling "Open" with "0" argument(s): "Cannot open database "Audit" requested by the login. The login failed.
Login failed for user 'ENTERPRISE-1\admin.user'."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:12 char:5
+     $Connection.Open()
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
connection's current state is closed."
At C:\IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException
LVL 1
leadthewayAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Try $command.ExecuteNonQuery instead of ExecuteReader.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
but I'm listed as a dbo under the database
(...)Login failed for user 'ENTERPRISE-1\admin.user'."
Being a database user doesn't really means that you can login to a SQL Server instance. For that you'll need to have the database user mapped to a SQL Server login (they are different objects).
Can you connect to the database with SSMS?
0
 
leadthewayAuthor Commented:
yes, i am using the same account i installed sqlexpress with...i think i found the issue though, i needed to specific the instance
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's the other thing I wanted to ask after knowing that you can connect through SSMS: if you can post the connection string that you're using.
0
 
leadthewayAuthor Commented:
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"
0
 
leadthewayAuthor Commented:
heres the full script
$connectionString = "Server=KCC-RPT-Admin\SQLEXPRESS;Database=Audit;User ID=report_admin; Password=Payday!5;"
$tableName = "FilePermissions"

function WriteSQL ($query)
{
    if ($debug -eq $true) {Write-Host $query}
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = $connectionString
    $Connection.Open()
    $Command = New-Object System.Data.SqlClient.SqlCommand
    $command.Connection = $Connection
    $command.CommandText = $query
    $command.ExecuteReader()
    $connection.Close()
}

$ErrorActionPreference = "Continue" 
$strComputer = $env:ComputerName 
$colDrives = Get-PSDrive -PSProvider Filesystem 
ForEach ($DriveLetter in $colDrives) { 
    $StartPath = "E:\Share" 
    Get-ChildItem -LiteralPath $StartPath -Recurse | 
    ForEach { 
      $FullPath = Get-Item -LiteralPath (Get-Item -LiteralPath $_.PSPath) 
      (Get-Item -LiteralPath $FullPath).GetAccessControl() | 
      Select * -Expand Access | 
      Select @{N='ServerName';E={$strComputer}}, 
             @{N='FullPath';E={$FullPath}}, 
             @{N='Type';E={If($FullPath.PSIsContainer -eq $True) {'D'} Else {'F'}}}, 
             @{N='Owner';E={$_.Owner}}, 
             @{N='Trustee';E={$_.IdentityReference}}, 
             @{N='Inherited';E={$_.IsInherited}}, 
             @{N='InheritanceFlags';E={$_.InheritanceFlags}}, 
             @{N='AceFlag';E={$_.PropagationFlags}}, 
             @{N='AceType';E={$_.AccessControlType}}, 
             @{N='AccessMasks';E={$_.FileSystemRights}} } |

             %{
                $query = "INSERT INTO $tableName (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,acetype,accessmasks) VALUES ('$($_.servername)','$($_.fullpath)','$($_.type)','$($_.owner)','$($_.trustee)','$($_.inherited)','$($_.inheritanceflags)','$($_.aceflag)','$($_.acetype)','$($_.accessmasks)')"
                WriteSQL $query
             }

    }
             

Open in new window


I think its authenticating but i'm getting different error now:

Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near 's'.
Unclosed quotation mark after the character string ')'."
At C:\ IT Scripts\AuditFilePermissions_single path.ps1:16 char:5
+     $command.ExecuteReader()
+     ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, the error is different now.
Try to print $query before the $command.ExecuteReader() line.
Btw, if you have an INSERT shouldn't be executed by ExecuteNonQuery instead of ExecuteReader command?
0
 
leadthewayAuthor Commented:
hmm ok so now i added the print $query

 print $query
    $command.ExecuteReader()
    $connection.Close()

Open in new window



now i don't get a red error, i now get invalid switch for Insert...

Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag
,acetype,accessmasks) VALUES ('FILE01','E:\Share\Accounting','D','ENTERPRISE-1\ACCOUNTING','CREATOR OWNER','False','ContainerInherit, ObjectInherit','InheritOnly','Allow','FullControl')"
0
 
leadthewayAuthor Commented:
looks like data did get into the table despite the invalid switch... I'll swap out commands
0
 
leadthewayAuthor Commented:
Invalid switch - "INSERT INTO FilePermissions (servername,fullpath,type,owner,trustee,inherited,inheritanceflags,aceflag,ace
type,accessmasks) VALUES ('domain-FILE01','E:\Share\Accounting\2016 SOC 1 Audit','D','ENTERPRISE-1\user','ENTERPRISE-1\user','True','None','None','Allow','FullControl')"
1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Remove the print command since you don't need it anymore.
0
 
leadthewayAuthor Commented:
getting a bunch of 1 with the occasional errors.  I assume that means its working now?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not sure but at least main issue disappear.
I'm also a SQL Server guy so you might open a new question in development area to get better expertise answers.
0
 
leadthewayAuthor Commented:
yeah i ran a query against the table and the rows are increasing and if i look at the top 1000, theres data that i expect there.  Now to export this to a webpage somehow...thanks for the assist
0
 
Scott PletcherSenior DBACommented:
Btw, if that's a real password you posted, please ask E-E to edit out the password for you(!).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.