Resetting a binary password through query analyzer

• Oz-444951

  SSC Enthusiast

  Points: 187

  More actions

  May 31, 2007 at 11:45 am

  #68680

  Hi. As a new sysadmin, I've inherited a system built on SQL Server 2000, with a VB6 frontend. I have no formal experience with SQL, but you know how it is I need a password to the system, but the field which stores passwords in the users table is varbinary. I was trying a query to reset the admin password:

  update dbo.tblusers set password ='whatever' where userid = '1'

  and I get:

  "Disallowed implicit conversion from data type varchar to data type varbinary, table 'Investments.dbo.tblUsers', column 'PassWord'. Use the CONVERT function to run this query."

  I've researched the CONVERT function, but I don't understand the syntax. I'm thinking I could also try an insert statement to create a new user. Could anyone offer some guidance?

  Thanks.

• Lowell

  SSC Guru

  Points: 323743

  More actions

  June 1, 2007 at 6:09 am

  #710301

  here's an example I keep around for casting from nvarchar to varbinary and then back to nvarchar:

  the key is to use nvarchars... i beleive this fails if you try to use plain old varchar.

  DECLARE @var VARBINARY(128),

          @res NVARCHAR(64)

  SET @var = CAST(N'Hello World' AS VARBINARY(128))

  PRINT @var

  --results: 0x480065006C006C006F00200057006F0072006C006400

  SET @res = CAST(@var AS NVARCHAR(64))

  PRINT @res

  --results: Hello World

  --The same but using CONVERT:

  SET @var = CONVERT(VARBINARY(128), (N'Bananas and Oranges'))

  PRINT @var

  --results: 0x420061006E0061006E0061007300200061006E00640020004F00720061006E00670065007300

  SET @res = CONVERT(NVARCHAR(64),@var)

  PRINT @res

  --results: Bananas and Oranges

   

  [edited]

  forgot to say, so in your example, you would change your statement to be this:

  update dbo.tblusers set password =CAST(N'whatever' AS VARBINARY(128)) where userid = '1'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!