What data can be stored in varbinary data type of SQL Server?

sql sql-server types varbinary
Fraz Sundal picture Fraz Sundal · Jul 18, 2010 · Viewed 80k times · Source

I have a table in which the userpassword field have varbinary datatype, So I'm confused that in which form should I save the data into userpassword field because when I save varchar data it gave me error.

Answer

Andomar picture Andomar · Jul 18, 2010

A varbinary column can store anything. To store a string in it, you'd have to cast it to varbinary:

declare @t table (id int identity, pwd varbinary(50))
insert into @t (pwd) values (cast('secret' as varbinary(50)))

But for a password, a varbinary column usually stores a hash of some kind. For example, a SHA1 hash using the HashBytes function:

insert into @t (pwd) values (HashBytes('sha1', 'secret'));

Storing a one-way hash instead of the real password is more secure. You can check if the password matches:

select * from @t where pwd = HashBytes('sha1', 'secret')

But there is no way you can retrieve the password by looking at the table. So only the end user knows his password, and not even the DBA can retrieve it.

Related questions

Difference between numeric, float and decimal in SQL Server

What are the differences between numeric, float and decimal datatypes and which should be used in which situations? For any kind of financial transaction (e.g. for salary field), which one is preferred and why?

Read More
How to store decimal values in SQL Server?

I'm trying to figure out decimal data type of a column in the SQL Server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc I assigned decimal(18, 0) to the column data type but this not allowing me to store …

Read More
What is SYSNAME data type in SQL Server?

What is the SQL Server SYSNAME data type for? BOL says: The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. but I don't really get that. Is there a use-case you …

Read More