Solved

Alter login with a stored hashed password in a stored procedure

Posted on 2008-06-20
6
749 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to extract a "coded date" from a string field? 4 59
MS SQL order by with "over" statement and row_number() 11 47
How to simplify my SQL statement? 14 52
CROSS APPLY 4 51
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 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