Solved

Alter login with a stored hashed password in a stored procedure

Posted on 2008-06-20
6
753 Views
Last Modified: 2010-04-21
In SQL 2005 how do I use ALTER LOGIN MyLogin WITH PASSWORD = 0x08456374783836283839378 HASHED within a stored procedure to alter a users password based on a hash password. What we need to do is ghost a login to establish a users issues with an application then change their password back to their own password once the investigation has been completed. We used be able to store the hashed password in a table then apply the new password, do the work then update sysxlogins with the stored hashed password. Now we can't do that (for good reason) so how can I acheive this?
0
Comment
Question by:nicecats
  • 3
  • 3
6 Comments
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 21829461
You should use EXECUTE AS LOGIN='ThatLogin'

That way, you can impersonate them, but you don't need to change their password at all.

Rob
0
 

Author Comment

by:nicecats
ID: 21829714
That's almost there, tried running the procedure that he was having problems with but got no error. What we need to do is to go through the application as him which could run several procedures one after another. However I have seen the use of Grant Impersonate which maybe of some use in impersonation of the account in question if that is the case I would much appreciate an example of how this is acheived.

Jonathon
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 21837104
Do you mean by running the application using the "Run as..." option that appears on shortcuts when you right-click them? So... hit 'Start' in Windows, and try right-clicking on something and choosing "Run as". Provide the login details of the user, and you'll be running the app as them.

Rob
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:nicecats
ID: 21837650
Unfortunately it's a web application which users pass their username and password into it. However my original problem seems to have been service pack based. On a SQL Server 2005 SP1 machine
ALTER LOGIN MyLogin WITH PASSWORD = 0x08456374783836283839378 HASHED simply doesn't work, however on a SQL Server 2005 SP2 machine this works fine and is the solution to the problem. But as you were kind enough to respond with an alternative that also solved my problem you can have the point.

Thanks

Jonathon
0
 

Author Closing Comment

by:nicecats
ID: 31469069
Thanks for the help Rob.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 21839498
:) Pleased to be of assistance
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question