Solved

Alter login with a stored hashed password in a stored procedure

Posted on 2008-06-20
6
752 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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