Well, you didn't pay attention to the change of the password parameter type. The whole philosophy and idea and reasoning was to get away with the conversion problem to binary inside the [AUTHENTICATION].[spADDNEWUSER] procedure.
You can decide whether that even extends to the EXEC getting this passed in from any outside system. For testing purposes you can't do just what I did:
Code:
DECLARE @pPASSWORD VARBINARY(MAX) = [b]CONVERT(Varbinary(MAX), N'Test123')[/b]
Either you read what I already wrote, or here it's once more: Even if you define the password to be varchar throughout your code, the password input from any frontend can change in time and can be any kind of encoding from ASIII, ANSI, Unicode and several variants like UTF-8, UTF-16, UCS-2. Just think by the rule of encapsulation, you don't need to know this when the input is binary and the outside world ensures the user input password is already converted to binary before it enters your routine. The forntend knows best and has to know what encoding the password is and if that changes has to ensure you still get the same input. So on top of encapsulation think of separation of concerns. The encoding of the password should NOT be made your concern, it's a frontend topic only. Frontend and Backend usually should also be separated by a third middle tier. So the encoding of the password as text input should at maximum arrive in the middle tier but surely not be hardwired into the backend layer,
These are OOP programming basics.
Say what you like, say any outside module will also use the same encoding all the time, say divers used to connect will ensure the password text arrives as SQL Server N'Unicodestring' Unicode variant.
Do you remember the initial statement about your problem? That this all worked already but not on this server? And the only pointer still is differences in the Unicode encoding, something related to that. And the solution is to push this problem outside your scope because it is not your scope.
You only make it your problem, if you'd insist to be able to let this parameter be NVARCHAR. You could then say this moves the problem to the developer using your database, providing user and password in the correct encoding. But you see what even already happened while this isn't already involving any outside client, just your testing.
Where does this end? Well, at the frontend layer, obviously, where the knowledge about the encoding is. If a web application developer decides to change a website from UTF-8 to Latin-1 then he has to deal with the problem of users not being able to enter Unicode characters in their passwords anymore. And it is his responsibility, clearly. If he decides to change from UTF-8 to UCS-2 or UTF-16 it again becomes his responsibility the binary password he passes forward to you stays. It's hard enough even for the programmers responsible for their tier to take this into account, but it surely would just lead to pointing fingers, if you make this a thing extending into your tier, the backend.
Bye, Olaf.
Olaf Doschke Software Engineering