by Pat Richard at 2012-09-11 14:49:59
Running a query from a server against several servers that have SQL Express on them. It gets the data fine from all of the remote servers, but when it tried to get data from the server the script is running on, I get that error. The problem follows the script - no matter which server I run it on, I get the error for the local server, but the remote servers are fine. The function that’s throwing the error is:by cmille19 at 2012-09-11 15:17:12function Get-Data {
param (
[string]$sipAddr = $null,
[string]$server,
[string]$ClientVersion
)
##############################################################################################
# Went to using a named parameter for this function due to the
# way Powershell does its thing with parameter passing, which
# is NOT GOOD! At any rate, need to call this function
# as you would from a command line: Get-Data -sipAddr "value"
# -server "value"
#
# Also, assuming a value of NULL for the SIP address of an
# individual user, mostly to use this for finding overall
# values, only occasionally to seek specific users.
##############################################################################################
if ($sipAddr) {
[string]$whereClause = "where R.UserAtHost = '$sipAddr' "
} else {
if ($IncludeSystem){
[string]$whereClause = $null
}elseif ($ClientVersion){
[string]$whereClause = "where RE.ClientApp like '%$ClientVersion%' and R.UserAtHost not like 'RtcApplication-%' "
}else{
[string]$whereClause = "where R.UserAtHost not like 'RtcApplication-%' "
}
}
#Define SQL Connection String
[string]$connstring = "server=$server\rtclocal;database=rtcdyn;trusted_connection=true;"
#Define SQL Command
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, Reg.Fqdn<br> From rtcdyn.dbo.RegistrarEndpoint RE
Inner Join<br> rtc.dbo.Resource R on R.ResourceId = RE.OwnerId
Inner Join<br> rtcdyn.dbo.Registrar Reg on Reg.RegistrarId = RE.PrimaryRegistrarClusterId
$whereClause `
Order By ClientVersion, UserName"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connstring
$connection.Open()
$command.Connection = $connection
$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $command
$results = New-Object System.Data.Dataset
$recordcount = $sqladapter.Fill($results)
$connection.Close()
return $Results.Tables[0]
} # end function Get-Data
and the line referenced in the error is line 57 of the function:$recordcount = $sqladapter.Fill($results)(near the bottom of the function)
What’s interesting is that this USUALLY works. It only seems to be sporadic. But I’ve seen it in different environments, and not sure what I can do.
I’m NOT a SQL guy, and this was cobbled together. Any insight is greatly appreciated.
This error means you don’t have permissions to the database object you’re selecting from. It could be that the login you’re using has access to the SQL Server instance, but does not have permissions to the database object (tables, views, stored procedure). More than likely you’re using local Windows administration account? In SQL Server versions prior to 2008 the local administrator also was a SQL Server sysadmin and so had access to all database object. It could be that the other SQL instances are 2005?by Pat Richard at 2012-09-12 06:57:16
This is purely a permissions issues and without knowing the details of how your login is setup I can only guess, but let’s try running this query to see if you’re account is a SQL Server administrator on the instance you’re getting select permission denied:
" SELECT IS_SRVROLEMEMBER (‘sysadmin’)"
It will return 1 if it is a member or 0 if not.
But what’s interesting is that I can connect to the server if I just run the script from another box, using the SAME account.by cmille19 at 2012-09-12 08:53:26
Are you certain it’s the same box you’re connecting to? I’ve seen people create hosts file entries or set client alias to (using start run cliconfg) to have a server name resolve a different name or different instance on from another server. In my company the SharePoint guys use hosts entries to alias database server very frequent because it is very difficult to change connection strings in SharePoint after it’s been deployed.by Pat Richard at 2012-09-12 11:06:22
I would try running the following query from both local server and remote server and see if you get the same results.
"SELECT SUSER_NAME() AS ‘suser’, SERVERPROPERTY(‘ServerName’) AS ‘servername’, IS_SRVROLEMEMBER(‘sysadmin’) AS ‘issysadmin’"
Okay, so checking that, it mimics what my error reports. When checking on the local server, I get 0, but checking the same server remotely from another, I get 1. And I see that for all servers I test. Is there a connection setting I might be missing? Some SQL Express config setting that blocks that locally?by cmille19 at 2012-09-12 13:19:26
The suser and servername, retrieved by the query I provided return the same values and issysadmin returns 0 or 1 depending on local or remote?by Pat Richard at 2012-09-12 13:31:24
"SELECT SUSER_NAME() AS ‘suser’, SERVERPROPERTY(‘ServerName’) AS ‘servername’, IS_SRVROLEMEMBER(‘sysadmin’) AS ‘issysadmin’"by cmille19 at 2012-09-12 14:18:13
yields 0 when targetting the local server, and 1 when targetting a remote server. Doesn’t matter which server I run it from results are the same each time. So, of the 4 servers I’m testing, running the script from the first server will yield
server 1: 0
server 2: 1
server 3: 1
server 4: 1
if I run the same query from server 2, I get
server 1: 1
server 2: 0
server 3: 1
server 4: 1
"SELECT IS_SRVROLEMEMBER (‘sysadmin’)"
yields the same results.
The only way you’ll get different results for sysadmin portion of the query is if the Windows credential or server instance is different. Whether it runs locally or remotely does not effect the permissions assigned to a login.by Pat Richard at 2012-09-12 14:44:34
The results you’ve provided shows only the issysadmin column, so I can’t tell. Can provide the entire result set? Feel free to sanitize the username/servername to some extent.
Okay - just dawned on me that there might be an assumption that’s my fault.by cmille19 at 2012-09-12 15:09:44
Each of the servers is running a LOCAL copy of SQL Express. Each was installed exactly the same.
BUT - the issue remains that looking at the instance on one server yields different results whether I’m looking at it from the local machine or from a remote machine.
Here are the results, as run from the first server listed:
suser servername issysadmin
----- ---------- ----------
domain\myuser SERVER1\RTCLOCAL 0
domain\myuser SERVER2\RTCLOCAL 1
domain\myuser SERVER3\RTCLOCAL 1
domain\myuser SERVER4\RTCLOCAL 1
And the results when run from the second server in the list:
suser servername issysadmin
----- ---------- ----------
domain\myuser SERVER1\RTCLOCAL 1
domain\myuser SERVER2\RTCLOCAL 0
domain\myuser SERVER3\RTCLOCAL 1
domain\myuser SERVER4\RTCLOCAL 1
There is no SQL Management tools installed on these servers. I’m using just a quick PowerShell query:# use for Lync front end servers
function Get-CsIsSQLAdmin {
#Requires -Version 2.0
[CmdletBinding(SupportsShouldProcess = $True)]
param (
[parameter(ValueFromPipeline = $false, ValueFromPipelineByPropertyName = $true, Mandatory = $false)]
[ValidateNotNullOrEmpty()]
[string]$server = ([System.Net.Dns]::GetHostByName($env:computerName)).hostname
)
[string]$connstring = "server=$server\rtclocal;database=rtcdyn;trusted_connection=true;"
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT SUSER_NAME() AS 'suser', SERVERPROPERTY('servername') AS 'servername', IS_SRVROLEMEMBER('sysadmin') AS 'issysadmin'"
# $command.CommandText = "SELECT IS_SRVROLEMEMBER ('sysadmin')"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connstring
$connection.Open()
$command.Connection = $connection
$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $command
$results = New-Object System.Data.Dataset
$recordcount = $sqladapter.Fill($results)
$connection.Close()
return $Results.Tables[0]
} # end function Get-IsSQLAdmin
Well, this is odd.
One thing you could try is explicitly adding the user as SQL Server sysadmin. From a regular cmd.exe elevated prompt you can create a temporary scheduled task to add users/groups to local SQL Server instance. I’ve got a batch file here:
http://sev17.com/2011/10/gaining-sql-se … in-access/