|
Home > Archive > Microsoft SQL Server forum > December 2005 > Stored Procedure only works in when app uses trusted security
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored Procedure only works in when app uses trusted security
|
|
| ZRexRider 2005-12-23, 11:23 am |
| 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 2005-12-23, 8:24 pm |
| 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" <jerryg@ptd.net> wrote in message
news:1135358521.631291.106350@g44g2000cwa.googlegroups.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 2005-12-23, 8:24 pm |
| ZRexRider (jerryg@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| ZRexRider 2005-12-23, 8:24 pm |
| 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:[color=darkred
]
> 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" <jerryg@ptd.net> wrote in message
> news:1135358521.631291.106350@g44g2000cwa.googlegroups.com...
| |
| ZRexRider 2005-12-23, 8:24 pm |
| 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('\',REVERS
E(suser_sname()))-1)
UPDATE
tbTest
SET
UpdateUserName=@strU
serName,
UpdateDate=GETDATE()
WHERE
tblTest.ID IN (SELECT inserted.ID FROM inserted)
SET NOCOUNT OFF
END
| |
| Erland Sommarskog 2005-12-24, 9:23 am |
| ZRexRider (jerryg@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('',REVERS
E(suser_sname()))-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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| ZRexRider 2005-12-26, 9:23 am |
| Thank You Erland - Merry Christmas to you!
|
|
|
|
|