A severe error occurred on the current command when running stored

G

Guest

I am trying to execute the following stored procedure:

sp_help_job on msdb on a SQL Server 2000 installation under the context of a
user that is just a standard sql server login. Not a sysadmin.

If i run it under the context of a sysadmin i get results. However, if i run
it under a basic user i get:

A severe error occurred on the current command. The results, if any, should
be discarded.

I thought it might be related to not specifying parameters but i did that
too. Here is my code:

public void ThisWillFail()
{
string connectionString = "Data Source=server;Initial
Catalog=msdb;Integrated Security=false;User
ID=basicuser;Password=basicuser;Application Name=TESTAPP;Persist Security
Info=False;Connect Timeout=30;";

SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("sp_help_job", sqlConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add("@job_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar, 9);
sqlCommand.Parameters.Add("@job_type", SqlDbType.VarChar, 12);
sqlCommand.Parameters.Add("@owner_login_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@subsystem", SqlDbType.NVarChar, 40);
sqlCommand.Parameters.Add("@category_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@enabled", SqlDbType.TinyInt);
sqlCommand.Parameters.Add("@execution_status", SqlDbType.Int);
sqlCommand.Parameters.Add("@date_comparator", SqlDbType.Char, 1);
sqlCommand.Parameters.Add("@date_created", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@date_last_modified", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@description", SqlDbType.NVarChar, 512);

SqlDataReader dr = null;

try
{
sqlConnection.Open();
dr = sqlCommand.ExecuteReader();

while (dr.Read())
{
System.Diagnostics.Debug.WriteLine(dr["name"]);
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}

if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
}

Any help is greatly appreciated.

-Brooke
 
V

Val Mazur

Hi,

Sine it is working fine with admin, I would suspect it is related to
permissions issues. Here is what I have found in a BOL for SQL Server. I
think it might help you

"When sp_help_job is invoked by a user who is a member of the sysadmin fixed
server role, sp_help_job will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, sp_help_job will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, sp_help_job will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and sp_help_job is always executed under the security context
of the Windows 9.x user who started SQL Server."


--
Val Mazur
Microsoft MVP


Macromullet said:
I am trying to execute the following stored procedure:

sp_help_job on msdb on a SQL Server 2000 installation under the context of
a
user that is just a standard sql server login. Not a sysadmin.

If i run it under the context of a sysadmin i get results. However, if i
run
it under a basic user i get:

A severe error occurred on the current command. The results, if any,
should
be discarded.

I thought it might be related to not specifying parameters but i did that
too. Here is my code:

public void ThisWillFail()
{
string connectionString = "Data Source=server;Initial
Catalog=msdb;Integrated Security=false;User
ID=basicuser;Password=basicuser;Application Name=TESTAPP;Persist Security
Info=False;Connect Timeout=30;";

SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("sp_help_job", sqlConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add("@job_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar, 9);
sqlCommand.Parameters.Add("@job_type", SqlDbType.VarChar, 12);
sqlCommand.Parameters.Add("@owner_login_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@subsystem", SqlDbType.NVarChar, 40);
sqlCommand.Parameters.Add("@category_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@enabled", SqlDbType.TinyInt);
sqlCommand.Parameters.Add("@execution_status", SqlDbType.Int);
sqlCommand.Parameters.Add("@date_comparator", SqlDbType.Char, 1);
sqlCommand.Parameters.Add("@date_created", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@date_last_modified", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@description", SqlDbType.NVarChar, 512);

SqlDataReader dr = null;

try
{
sqlConnection.Open();
dr = sqlCommand.ExecuteReader();

while (dr.Read())
{
System.Diagnostics.Debug.WriteLine(dr["name"]);
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}

if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
}

Any help is greatly appreciated.

-Brooke
Click to expand...
 
G

Guest

It runs fine in query analyzer using the exact same credentials, and it
returns results, corresponding to the jobs owned by the account in question,
as it should. I did a trace in profiler and i can see it going through parts
of the procedure when i run it from the sqlclient, but i think its bombing on
the parsing of resulting TDS stream. It smells like a bug to me.

Val Mazur said:
Hi,

Sine it is working fine with admin, I would suspect it is related to
permissions issues. Here is what I have found in a BOL for SQL Server. I
think it might help you

"When sp_help_job is invoked by a user who is a member of the sysadmin fixed
server role, sp_help_job will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, sp_help_job will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, sp_help_job will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and sp_help_job is always executed under the security context
of the Windows 9.x user who started SQL Server."


--
Val Mazur
Microsoft MVP


Macromullet said:
I am trying to execute the following stored procedure:

sp_help_job on msdb on a SQL Server 2000 installation under the context of
a
user that is just a standard sql server login. Not a sysadmin.

If i run it under the context of a sysadmin i get results. However, if i
run
it under a basic user i get:

A severe error occurred on the current command. The results, if any,
should
be discarded.

I thought it might be related to not specifying parameters but i did that
too. Here is my code:

public void ThisWillFail()
{
string connectionString = "Data Source=server;Initial
Catalog=msdb;Integrated Security=false;User
ID=basicuser;Password=basicuser;Application Name=TESTAPP;Persist Security
Info=False;Connect Timeout=30;";

SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("sp_help_job", sqlConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add("@job_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar, 9);
sqlCommand.Parameters.Add("@job_type", SqlDbType.VarChar, 12);
sqlCommand.Parameters.Add("@owner_login_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@subsystem", SqlDbType.NVarChar, 40);
sqlCommand.Parameters.Add("@category_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@enabled", SqlDbType.TinyInt);
sqlCommand.Parameters.Add("@execution_status", SqlDbType.Int);
sqlCommand.Parameters.Add("@date_comparator", SqlDbType.Char, 1);
sqlCommand.Parameters.Add("@date_created", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@date_last_modified", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@description", SqlDbType.NVarChar, 512);

SqlDataReader dr = null;

try
{
sqlConnection.Open();
dr = sqlCommand.ExecuteReader();

while (dr.Read())
{
System.Diagnostics.Debug.WriteLine(dr["name"]);
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}

if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
}

Any help is greatly appreciated.

-Brooke
Click to expand...
Click to expand...
 
V

Val Mazur

I will try to check it.

--
Val Mazur
Microsoft MVP


Macromullet said:
It runs fine in query analyzer using the exact same credentials, and it
returns results, corresponding to the jobs owned by the account in
question,
as it should. I did a trace in profiler and i can see it going through
parts
of the procedure when i run it from the sqlclient, but i think its bombing
on
the parsing of resulting TDS stream. It smells like a bug to me.

Val Mazur said:
Hi,

Sine it is working fine with admin, I would suspect it is related to
permissions issues. Here is what I have found in a BOL for SQL Server. I
think it might help you

"When sp_help_job is invoked by a user who is a member of the sysadmin
fixed
server role, sp_help_job will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, sp_help_job will impersonate the SQL Server Agent
proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, sp_help_job will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and sp_help_job is always executed under the security
context
of the Windows 9.x user who started SQL Server."


--
Val Mazur
Microsoft MVP


Macromullet said:
I am trying to execute the following stored procedure:

sp_help_job on msdb on a SQL Server 2000 installation under the context
of
a
user that is just a standard sql server login. Not a sysadmin.

If i run it under the context of a sysadmin i get results. However, if
i
run
it under a basic user i get:

A severe error occurred on the current command. The results, if any,
should
be discarded.

I thought it might be related to not specifying parameters but i did
that
too. Here is my code:

public void ThisWillFail()
{
string connectionString = "Data Source=server;Initial
Catalog=msdb;Integrated Security=false;User
ID=basicuser;Password=basicuser;Application Name=TESTAPP;Persist
Security
Info=False;Connect Timeout=30;";

SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand("sp_help_job", sqlConnection);

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.Parameters.Add("@job_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add("@job_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar, 9);
sqlCommand.Parameters.Add("@job_type", SqlDbType.VarChar, 12);
sqlCommand.Parameters.Add("@owner_login_name", SqlDbType.NVarChar,
128);
sqlCommand.Parameters.Add("@subsystem", SqlDbType.NVarChar, 40);
sqlCommand.Parameters.Add("@category_name", SqlDbType.NVarChar, 128);
sqlCommand.Parameters.Add("@enabled", SqlDbType.TinyInt);
sqlCommand.Parameters.Add("@execution_status", SqlDbType.Int);
sqlCommand.Parameters.Add("@date_comparator", SqlDbType.Char, 1);
sqlCommand.Parameters.Add("@date_created", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@date_last_modified", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@description", SqlDbType.NVarChar, 512);

SqlDataReader dr = null;

try
{
sqlConnection.Open();
dr = sqlCommand.ExecuteReader();

while (dr.Read())
{
System.Diagnostics.Debug.WriteLine(dr["name"]);
}
}
catch (SqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}

if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
}

Any help is greatly appreciated.

-Brooke
Click to expand...
Click to expand...
Click to expand...
 
Top