 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / August 2008SYSTEM_USER automatic RTRIM of trailing spaces in 2000, but not 2005?
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! > 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 [quoted text clipped - 3 lines] > 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.  SignatureErland Sommarskog, SQL Server MVP, esquel@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/prodinfo/previousversions/books.mspx > (brian.j.par...@gmail.com) writes: > > Hey all, I've noticed an obscure little quirk: it appears that if you [quoted text clipped - 4 lines] > 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. > 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? SignatureErland Sommarskog, SQL Server MVP, esquel@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/prodinfo/previousversions/books.mspx > Hm, I'm not sure that I see any change. I rand this on SQL 2000 SP4: > [quoted text clipped - 11 lines] > > <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... > 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 [quoted text clipped - 5 lines] > 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). SignatureErland Sommarskog, SQL Server MVP, esquel@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/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.