question

rsheik avatar image
rsheik asked

Powershell SMO is not Working for Non_Admin User

Hi, I have powershell script to genarate scripts for all objects within a given database. I tried it in my local system where I am the Administrator it is working perfect , but when I run same code on other system (where I am a standard user, not administrator) it is throwing error. (screenshot) This is my code.. Function powershell_main { # defining parameters [CmdletBinding()] param( [Parameter(Mandatory=$True)] [string[]]$server, [Parameter(Mandatory=$True)] [string[]]$user, [Parameter(Mandatory=$True)] [string[]]$password, [Parameter(Mandatory=$True)] [string[]]$databases, [Parameter(Mandatory=$True)] [string[]]$output_path, [Parameter(Mandatory=$True)] [string[]]$DBSchema ) BEGIN { Write-Host "Process started at :" (Get-Date) } PROCESS { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server #This sets the connection to mixed-mode authentication $srv.ConnectionContext.LoginSecure=$false; #This sets the login name $srv.ConnectionContext.set_Login($user); #This sets the password $srv.ConnectionContext.set_Password($password) try { write-host "Testing Connection with server:" $server # if we don't have connection this will throw exception write-host $srv.Databases } catch { write-host "Caught an exception:" write-host "Exception Type: $($_.Exception.GetType().FullName)" write-host "Exception Message: $($_.Exception.Message)" Write-Host "Server not found or Invalid credentials " Exit } # here onwards total process runs for each db # Iterate on each database # creating dictionary for holding result of process for each db # so that we can print dictionary as a table # $db_result = [ordered]@{} # $db_result = @{} $db_result = "Database `t`t` Result" foreach ($db in $databases) { try { $success_fail = "Failed" $database = $db write-host "Connected to the server: " $server write-host "Checking whether database" $database "Exists or not" # get all databases and check whether our database is existing in the list $ck_Database = $srv.Databases | where {$_.Name -eq $database} if ($ck_Database -eq $null) { write-host "Database " $database "does not exists" continue } write-host "Database" $database " Exists..." write-host "Checking whether Schema" $dbSchema "Exists or not" # get all Schemas and check whether our Schema is existing in the list $ck_Schema = $ck_Database.Schemas | where {$_.Name -match $dbSchema} if ($null -eq $ck_Schema) { write-host "Schema: " $dbSchema "does not exists for given credentials" continue } write-host "Schema: " $dbSchema " exists" write-host "Generating Scripts for " $database # setting folder structure $schema = $dbSchema $table_path = "$output_path\"+$schema+"\"+$database+"\Table\" $storedProcs_path = "$output_path\"+$schema+"\"+$database+"\StoredProcedure\" $views_path = "$output_path\"+$schema+"\"+$database+"\View\" $udfs_path = "$output_path\"+$schema+"\"+$database+"\UserDefinedFunction\" $textCatalog_path = "$output_path\"+$schema+"\"+$database+"\FullTextCatalog\" $udtts_path = "$output_path\"+$schema+"\"+$database+"\UserDefinedTableTypes\" $db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") $tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table") $scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server) # Get the database and table objects $db = $srv.Databases[$database] #$Just_Check = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $views = $db.Views | Where-object { $_.schema -eq $schema } $udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } $catlog = $db.FullTextCatalogs $udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema } # Set scripter options to ensure only data is scripted $scripter.Options.ScriptSchema = $true; $scripter.Options.ScriptData = $false; #Exclude GOs after every line $scripter.Options.NoCommandTerminator = $false; $scripter.Options.ToFileOnly = $true $scripter.Options.AllowSystemObjects = $false $scripter.Options.Permissions = $false $scripter.Options.DriAllConstraints = $true $scripter.Options.SchemaQualify = $true $scripter.Options.AnsiFile = $true $scripter.Options.SchemaQualifyForeignKeysReferences = $true $scripter.Options.Indexes = $true $scripter.Options.DriIndexes = $true $scripter.Options.DriClustered = $true $scripter.Options.DriNonClustered = $true $scripter.Options.NonClusteredIndexes = $true $scripter.Options.ClusteredIndexes = $true $scripter.Options.FullTextIndexes = $true $scripter.Options.EnforceScriptingOptions = $true function CopyObjectsToFiles($objects, $outDir) { if (-not (Test-Path $outDir)) { [System.IO.Directory]::CreateDirectory($outDir) } foreach ($o in $objects) { if ($o -ne $null) { $schemaPrefix = "" if ($o.Schema -ne $null -and $o.Schema -ne "") { $schemaPrefix = $o.Schema + "." } $scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql" Write-Host "Writing " $scripter.Options.FileName $scripter.EnumScript($o) } } } # Output the scripts CopyObjectsToFiles $tbl $table_path CopyObjectsToFiles $storedProcs $storedProcs_path CopyObjectsToFiles $views $views_path CopyObjectsToFiles $catlog $textCatalog_path CopyObjectsToFiles $udtts $udtts_path CopyObjectsToFiles $udfs $udfs_path $success_fail = "Success" } catch { write-host "Caught an exception:" write-host "Exception Type: $($_.Exception.GetType().FullName)" write-host "Exception Message: $($_.Exception.Message)" write-host "Exception Occurred while generating scripts for " $database continue } finally { # inserting column name and lines $db_result = $db_result + "`n"+$database+"`t`t"+$success_fail } } } END { Write-Host "`nPrinting overall result for section ." Write-Host $db_result Write-Host "Process ended at :" (Get-Date) } } ![alt text][1] [1]: /storage/temp/2349-error.jpg Any Idea .. How to run this code being not a admin..
administrationpowershellsqlserver 2008r2smo
error.jpg (103.7 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
Pretty sure that's a permissions issue. The writes are going to a system drive. If the login that this runs under doesn't have permission to that drive, you'll get the error. Change the location to someplace that they have permission to write to and it ought to work then.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.