A severe error occurred on the current command when running stored

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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...
 
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...
 
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...
 
Back
Top