June 20, 2011 at 6:16 am
Hi,
I'm running Windows 2008 R2 Enterprise and SQL Server 2008 R2 Enterprise and I'm executing the following script via PowerShell v2.0 to script off all the SQL Server Scheduled Jobs for a given instance. If the account under which the script executes has sysAdmin rights on the SQL Server instance the process completes correctly. However, if I remove the sysAdmin access and assign any other form of permissions on the instance the PowerShell script executes but returns no information (not even an error).
Just wondered if anyone had any bright ideas as I'm starting to run out of options 🙂
param ([string]$Principal)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$ErrorActionPreference = "Stop"
$ps = New-Object Microsoft.SqlServer.Management.Smo.Server $Principal
if (-not $ps.Databases.Count)
{
"Could not connect to $Principal"
return
}
# Script Jobs
$ps.JobServer.Jobs | ? {$_.Name -notmatch "Database Mirroring"} | % {$_.Script()}
# Script sys.messages
$sql = @"
select 'exec sp_addmessage @msg_id = ' + convert(varchar(10), message_id) + ', @severity = ' + convert(varchar(10), severity) + ', @msgtext = ''' + text + ''', @replace = ''replace''' cmd
from sys.messages
where message_id > 50000
"@
$msgs = $ps.Databases["master"].ExecuteWithResults($sql).Tables[0].Rows
foreach ($m in $msgs)
{
$m.cmd
}
Thanks in advance.
www.sqlAssociates.co.uk
June 20, 2011 at 10:31 am
I don't know about *bright* but a try/catch might do some good. If an exception is thrown control will change to the catch block, the message will be output to the console and the script will end
try {
# insert your entire script except the input parameter declaration here
}
catch {
"Something strange occurred: $_"
}
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply