Hoe to get Encrypted Password value varbinary field

I just converted from SQL 7 to SQL 2000 but have one issue.
My login table contains a password field that is a varbinary type. I
encrypted the value by using the encrypt('password') when inserting users
into this table.
How can I see the actual value of the password field. Is there a way to
decrypt?
For some reason, all the users passwords don't work. If I redo each one, it
works but there are too many to redo manually."yodakt" <dev1on11@.gmail.com> wrote in message
news:6E45D35E-970E-4656-AECF-B192BD1035D7@.microsoft.com...
>I just converted from SQL 7 to SQL 2000 but have one issue.
> My login table contains a password field that is a varbinary type. I
> encrypted the value by using the encrypt('password') when inserting users
> into this table.
> How can I see the actual value of the password field. Is there a way to
> decrypt?
> For some reason, all the users passwords don't work. If I redo each one,
> it
> works but there are too many to redo manually.
Try this:
SELECT CAST(myPassword AS NVARCHAR(100))
FROM myTable
Where myPassword is the VARBINARY column containing the passwords.

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO