question

This setup is begging for problems, unfortunately. Let me enumerate the ways: 1. SHA1 is not a particularly strong hashing algorithm. One round of SHA1 can be broken pretty easily with a rainbow table or with a GPU farm. 2. You have a plaintext password already on the table. That makes hashing entirely moot. There is exactly **one** valid scenario for this: you're fixing historically bad code and will drop the column very, very soon. 3. If you do use a hashing algorithm (which you should), you probably should use something like PBKDF2. It's not built into SQL Server, but [Troy Hunt has a great blog post on the topic][1] if you are in a .NET environment. This setup lets you do solid security practicies, such as salting, key stretching, and using an extended number of rounds. 4. Hashing isn't encrypting. The "passwordencrypt" field is a bit of a misnomer. For password storage, hashing is superior to encrypting because a hash is supposed to be a one-way function; this means that your login validation mechanism can operate without direct knowledge of the plaintext password. This really isn't something that SQL Server is well-equipped to do on its own. SHA1 and MD5 are very fast comparison hashes. They're meant to act as checksums rather than security, and so modern hardware can rip right through them, making your "secure" hash rather insecure in the event that somebody gets ahold of the table with hashed passwords (like what happened to LinkedIn). Of course, that's assuming that the plain-text Password field isn't there... But if you do want to go ahead with hashing inside SQL Server (something I very highly recommend against, as you can tell), the query you're looking for is: update empl set passwordencrypt = hashbytes('sha1', password); That will update every row in your table. When a user changes his password, you could create a stored procedure that looks like: create procedure [yourprocedurename] @UserId int, @NewPassword varchar(128) as update empl set passwordencrypt = hashbytes('sha1', @NewPassword) where UserId = @UserId; After doing this, you could drop the plain-text password column and move the level of security from "none" to "practically none." For legitimate security, you'll either need to go outside of SQL Server or use the CLR to run a .NET DLL that uses bcrypt or PBKFD2. **Edit** The follow-up questions are for handling insertion and selection. These are separate procedures which are pretty simple insert and select statements that depend upon the exact contents of your table. The insert procedure would look something like: CREATE PROCEDURE [insertion procedure name] [columns as variables] AS insert into empl([columns]) values([columns as variables]); The select procedure is slightly more complex, only because it has two purposes: either simply looking up by username (to check for existence) or by username and password hash (to check that this is a valid login attempt). The easiest way would probably be to break it out into two separate procedures, although it's only slightly more complex to create one procedure which does both. I'll keep it simple. CREATE PROCEDURE [user lookup procedure name] @UserName varchar(whatever) AS select case when exists (select * from empl where username = @UserName) then 1 else 0 end; CREATE PROCEDURE [user validation procedure name] @UserName varchar(whatever), @PasswordHash varbinary(whatever) AS select top 1 [columns] from empl where username = @UserName and passwordencrypt = @PasswordHash; [1]: http://www.troyhunt.com/2012/07/stronger-password-hashing-in-net-with.html