Stored Procedure only works in when app uses trusted security

[ZRexRider]

Hi,

I have a .NET application that connects to a SQL 2000 database using
trusted security. It eventually calls a stored procedure that receives
3 parameters - nothing special.

If I simply change the connection string to use a valid Userid and
Password it still connects to the DB w/o problems but when it executes
the SP I get the following:

System.Data.SqlClient.SqlException: Invalid length parameter passed to
the substring function.

I change nothing but the login. Same store procedure, same parameters.

Any ideas?

[Dan Guzman]

Check the proc (or invoked trigger) to see SUSER_SNAME() is being parsed
into separate domain/account components. The code may fail with a standard
SQL Security connection because a '\' is assumed to be present.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"ZRexRider" <je****@ptd.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi,

I have a .NET application that connects to a SQL 2000 database using
trusted security. It eventually calls a stored procedure that receives
3 parameters - nothing special.

If I simply change the connection string to use a valid Userid and
Password it still connects to the DB w/o problems but when it executes
the SP I get the following:

System.Data.SqlClient.SqlException: Invalid length parameter passed to
the substring function.

I change nothing but the login. Same store procedure, same parameters.

Any ideas?

[Erland Sommarskog]

ZRexRider (je****@ptd.net) writes:

I have a .NET application that connects to a SQL 2000 database using
trusted security. It eventually calls a stored procedure that receives
3 parameters - nothing special.

If I simply change the connection string to use a valid Userid and
Password it still connects to the DB w/o problems but when it executes
the SP I get the following:

System.Data.SqlClient.SqlException: Invalid length parameter passed to
the substring function.

I change nothing but the login. Same store procedure, same parameters.

Any ideas?

A Christmas quiz? Maybe you should try rec.games.trivia in such case.

If this is not meant to be a quiz, please show us the code you are
having problem with, both the ADO .Net code and the stored procedure.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ZRexRider]

Dan you nailed it - SUSER_SNAME() in my trigger blows up because there
isn't a '\' and I didn't code for it.

Happy Holidays to you as well!
Dan Guzman wrote:

Check the proc (or invoked trigger) to see SUSER_SNAME() is being parsed
into separate domain/account components. The code may fail with a standard
SQL Security connection because a '\' is assumed to be present.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"ZRexRider" <je****@ptd.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi,

I have a .NET application that connects to a SQL 2000 database using
trusted security. It eventually calls a stored procedure that receives
3 parameters - nothing special.

If I simply change the connection string to use a valid Userid and
Password it still connects to the DB w/o problems but when it executes
the SP I get the following:

System.Data.SqlClient.SqlException: Invalid length parameter passed to
the substring function.

I change nothing but the login. Same store procedure, same parameters.

Any ideas?

[ZRexRider]

How can I make the following trigger work on both Trusted and
non-trusted connections?

CREATE TRIGGER trIU_tblTest
ON dbo.tblTest
FOR INSERT, UPDATE AS
BEGIN
DECLARE @strUserName VARCHAR(20)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- only want the userlogon name portion
SELECT @strUserName =
right(suser_sname(),CHARINDEX('\',REVERSE(suser_sn ame()))-1)
UPDATE
tbTest
SET
UpdateUserName=@strUserName,
UpdateDate=GETDATE()
WHERE
tblTest.ID IN (SELECT inserted.ID FROM inserted)
SET NOCOUNT OFF
END

[Erland Sommarskog]

ZRexRider (je****@ptd.net) writes:

How can I make the following trigger work on both Trusted and
non-trusted connections?

CREATE TRIGGER trIU_tblTest
ON dbo.tblTest
FOR INSERT, UPDATE AS
BEGIN
DECLARE @strUserName VARCHAR(20)

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- only want the userlogon name portion
SELECT @strUserName =
right(suser_sname(),CHARINDEX('\',REVERSE(suser_sn ame()))-1)

DECLARE @username nvarchar(256)
SELECT @username = SYSTEM_USER
IF charindex('\', @username) > 0
SELECT @username = substr(@username, charindex('\', @username) + 1,
len(@username)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ZRexRider]

Thank You Erland - Merry Christmas to you!