Stored Procedure Failing on a Specific User

Call to SQL Server stored procedure failing in certain cases

Paolo's comment, above, caused me to investigate the actual C# code that calls the stored procedure.

The code is convoluted for the sake of being convoluted, in my opinion.
There is a method is some class that handles all calls to stored procedures. I replaced that code with this basic code:

DataSet dataSet = new DataSet("ReturnDs");
using (var connection = new System.Data.SqlClient.SqlConnection(theConnectStg))
{
using (var command = new System.Data.SqlClient.SqlCommand(theStoreProcName, connection))
{
using (var dataAdapter = new System.Data.SqlClient.SqlDataAdapter(command))
{
command.CommandType = CommandType.StoredProcedure;
if (theParameterList != null)
{
foreach (String str1 in theParameterList.ToArray())
{
String parameterName = str1.Substring(0, str1.IndexOf(":"));
String str2 = str1.Substring(str1.IndexOf(":") + 1);
dataAdapter.SelectCommand.Parameters.Add(new SqlParameter(parameterName, SqlDbType.VarChar, 128));
dataAdapter.SelectCommand.Parameters[parameterName].Value = (object)str2;
}
}
dataAdapter.Fill(dataSet);
}
}
}
return dataSet;

To satisfy your curiosity, the theParameterList parameter is an array of parameters, each in the form "@variable:value". I'm not a fan, but I am stuck with the existing code for now.

So, why did the previous code fail for certain databases? I still do not know. I am curious, but I do not wish to spend any more time on this issue. My brain is tired.

Thanks for the clue, Paolo!

SQL- Login failed for user but that user cannot be found

Well, after a few days after I have tried everything, literally everything, found on the internet, uninstalling the SQL Server 2014 did the trick for me. Probably not the most recommended way, but it worked for me. Peace!

How to make stored procedure fail if condition is not met

As mentioned before, you can throw an error anytime in the code. You can use either RAISERROR or THROW (which I believe it was implemented in 2012). You don't need the variable or the count either.

IF NOT EXISTS(select * from DimTable where Incomplete = 1)
BEGIN
UPDATE DimTable2
SET Column1 = 'X';
END
ELSE
RAISERROR('There are no rows in DimTable where Incomplete equals 1', --Message
16, --Severity
1); --State

Or

IF NOT EXISTS(select * from DimTable where Incomplete = 1)
BEGIN
UPDATE DimTable2
SET Column1 = 'X';
END
ELSE
THROW 52000, --Error number must be between 50000 and 2147483647.
'There are no rows in DimTable where Incomplete equals 1', --Message
1; --State

You could also end the procedure without an error by using RETURN. If you return a value different to 0, you can use it as an indicator of some error in the application code.

IF NOT EXISTS(select * from DimTable where Incomplete = 1)
BEGIN
UPDATE DimTable2
SET Column1 = 'X';
END
ELSE
RETURN 10;

Why do I get: 'Login failed for user' when I run a stored procedure?

If you are referencing any remote servers (ie. databases on a different server), then you must make sure that the SQL user has access to those servers.

To check and fix this, in SSMS go to Server Objects > Linked Servers. If you don't have remote server listed there, then you'll have to add it. Also, you'll have to give a username and password that is accessible and has the proper permissions on both servers.

sql stored procedure execution failing because of permissions on tables

Are you using a "OpenQuery" inside the procedure ? try using fully qualified names including the linked server.

INSERT INTO Linked_Server.Database.schema.table_name
Select .............

SSRS call to stored procedure fails, Cannot find user 'dbo'

A stored procedure includes all statements in the batch after the CREATE PROCEDURE. So a proc like this

CREATE PROCEDURE USP_FOO
AS
BEGIN
SELECT * FROM FOO
END

GRANT EXECUTE ON USP_FOO TO SOMEUSER AS DBO;

Will attempt to execute to GRANT every time the procedure is run, and will fail when not run by dbo.

The script to create the procedure should have a batch separator before the grant. eg:

CREATE PROCEDURE USP_FOO
AS
BEGIN
SELECT * FROM FOO
END

GO

GRANT EXECUTE ON USP_FOO TO SOMEUSER AS DBO;


Related Topics



Leave a reply



Submit