SYSTEM_USER automatic RTRIM of trailing spaces in 2000, but not 2005?

[brian.j.parker]

Hey all, I've noticed an obscure little quirk: it appears that if you
use a login with trailing spaces on the name, SYSTEM_USER
automatically trims those trailing spaces in SQL Server 2000, but not
SQL Server 2005.

Anybody know if this change in behavior is documented? If it is
intentional?

Is there a "quick fix" to revert to the old behavior (to automatically
RTRIM the results of SYSTEM_USER in 2005) until code can be changed?

Thanks in advance for any replies!

[Erland Sommarskog]

(br************@gmail.com) writes:

Hey all, I've noticed an obscure little quirk: it appears that if you
use a login with trailing spaces on the name, SYSTEM_USER
automatically trims those trailing spaces in SQL Server 2000, but not
SQL Server 2005.

Anybody know if this change in behavior is documented? If it is
intentional?

It's surely intentional. The SQL 2000 behaviour strikes me as flat out
wrong. If the login "pelle " did something bad, he should be able to
put the blame on pelle without spaces.

Is there a "quick fix" to revert to the old behavior (to automatically
RTRIM the results of SYSTEM_USER in 2005) until code can be changed?

I would suggest that it would be incorrect to trim the result. I would
consider those spaces to be significant.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[brian.j.parker]

On Aug 21, 5:59*pm, Erland Sommarskog <[e-mail]:

*([e-mail]) writes:

Hey all, I've noticed an obscure little quirk: it appears that if you
use a login with trailing spaces on the name, SYSTEM_USER
automatically trims those trailing spaces in SQL Server 2000, but not
SQL Server 2005.

I would suggest that it would be incorrect to trim the result. I would
consider those spaces to be significant.

I agree, and I'm working out a long-term plan to consistently and
properly handle the logins. Unfortunately, we've got a ton of table
defaults and code built around this expectation. So until I can
actually do what's right, I was looking for a workaround that would
help clients who've moved to SQL Server 2005 avoid the quirky behavior
cropping up.

I don't think there is a workaround, but... I am surprised, because
even if the new behavior is correct (I agree it is) it is a breaking
change that doesn't seem to be documented anywhere.

[Erland Sommarskog]

(br************@gmail.com) writes:

I don't think there is a workaround, but... I am surprised, because
even if the new behavior is correct (I agree it is) it is a breaking
change that doesn't seem to be documented anywhere.

Hm, I'm not sure that I see any change. I rand this on SQL 2000 SP4:

sp_addlogin 'spacy ', nisse

Then I connected:

osql -P nisse -U "spacy "

And ran:

select '<' + SYSTEM_USER + '>'

The result:

<spacy >

So maybe there is some other difference here. Maybe you slept over
SP4, and migrated from SP3? Or maybe you rebuilt your tables from
scripts on SQL 2005, and had ANSI_PADDING ON, while you had it off
on SQL 2000?

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[brian.j.parker]

On Aug 25, 5:03*pm, Erland Sommarskog <[e-mail]:

Hm, I'm not sure that I see any change. I rand this on SQL 2000 SP4:

* *sp_addlogin 'spacy * ', nisse

Then I connected:

* *osql -P nisse -U "spacy * "

And ran:

* *select '<' + SYSTEM_USER + '>'

The result:

* *<spacy * >

That is very weird. I used Management Studio instead of osql, but I
just used your exact commands to create the 'spacy ' login on both
a 2005 and 2000 SQL Server here, and I do get different behavior; 2000
trims the trailing spaces.

I double checked @@version on the 2000 server and it gives me
8.00.2050, which is a post-SP4 hotfix. Since it's a direct create-
login-SYSTEM_USER check, it's not going into any tables I created
either...

[Erland Sommarskog]

(br************@gmail.com) writes:

That is very weird. I used Management Studio instead of osql, but I
just used your exact commands to create the 'spacy ' login on both
a 2005 and 2000 SQL Server here, and I do get different behavior; 2000
trims the trailing spaces.

I double checked @@version on the 2000 server and it gives me
8.00.2050, which is a post-SP4 hotfix. Since it's a direct create-
login-SYSTEM_USER check, it's not going into any tables I created
either...

I think I have it: on SQL 2000, SYSTEM_USER gives you string actually used
when you logged in. That is, the trailing spaces are included if you
provide them when you log in.

On SQL 2005, SYSTEM_USER gives the name as it stored in system tables, no
matter whether you provided trailing spaces or not when you logged in.

Note that this cuts both ways. You can provide trailing spaces when you
log in, even there are no trailing spaces in your login name, and
SQL Server will let you in. SYSTEM_USER will report those trailing spaces
on SQL 2000, but not on SQL 2005.

Apparently this changes is not documented, but there were a lot of
changes to system tables and security in SQL 2005, so it may not have
been intentional. MS may not have been aware of how it actually worked
in SQL 2000. After all, trailing spaces in login names does not appear
to be a very common thing...
To make this a little more confusing, SQL 2005 adds a new function,
original_login(). This function appears to always trim trailing spaces
(and never add any).

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx