Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Alter login with a stored hashed password in a stored procedure

Posted on 2008-06-20
6
Medium Priority
?
771 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Independent Software Vendors: 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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 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