0

I need to covert a string to a varbinary(85), which is the data type in my SQL Server Table (unable to change). The data is for a username and I need to compare the windows user name of a person who logs onto a website with this SQL data entry. An example of a login that I have in my database is:

0x0105000000000005150000004CCDD8292B55E7A8CD006C0E061F0012 which is the of the datatype varbinary(85).

Now I need to compare this to a string. What is the best way for me to convert a string to varbinary(85) so that I can get the exact same value.

I would preferably do this all in C# although I guess I could do it on the database end in SQL.

ANSWER:

using sql I could generate the name perfectly.

SELECT SUSER_SID(string)
user3757987
  • 1
  • 2
  • This looks like a hashed entry. What algorithm is being used to hash it? It's not just a simple `select convert(varbinary(85),name)` that's for sure. – Michael B Jun 19 '14 at 20:22
  • Not Completely sure. It's a Microsoft Product (SCVMM) so possibly SHA-256? And ya I've tried a bunch of stuff like that to no avail – user3757987 Jun 19 '14 at 20:27

1 Answers1

0

1) I would create an [unique] index on varbinary(85) column:

CREATE UNIQUE INDEX IUN_Users_UserName 
ON dbo.Users (UserName) -- Where UserName is varbinary(85)

2) I would convert current user name to varbinary(85) using the same algorithm 

DECLARE @binCurrentUserName VARBINARY(85);
SET @binCurrentUserName = .... convert here the current user name to VB(85) ...

and I would compare only varbinary values:

SELECT u.UserID
FROM dbo.Users u
WHERE u.UserName = @binCurrentUserName -- Please remember that UserName's type is VB(85)

Note: converting the varbinary values from UserName column to string (nvarchar) it's a bad idea because the SQL query will look like this 

SELECT u.UserID
FROM dbo.Users u
WHERE CONVERT(... u.UserName ... ) = @nvarcharCurrentUserName

and because will be a function (ex. CONVERT) applied on UserName column this will block Index Seek => the execution plan of this query will contain an Index Scan operator.

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57