May 9, 2005 at 9:51 pm
A developer would like to run a script as a certain user, but no one knows what the password is. Of course I could change it to something, but he suspects other jobs would break if I do.
Does anyone know of a way I can get the encrypted value, so I can then change the password, login as that user, run the script, and then change the password back to the original value?
I know you can do it in Oracle.
Thanks in anticipation,
Jules
May 10, 2005 at 5:52 am
If we talk about development box, you can simply store sysxlogins.password value for the user in working table (one column varbinary-256) then change the password, run your job and then update sysxlogins.password back to the original value from the working table. It does work, but don't do this in production.
If you think that the password is not very sophisticated one, you could try comparing the password to a dictionary using PWDCOMPARE function.
May 10, 2005 at 6:00 am
I don't know if this will help, but when I move users between Instances, I use this link:
http://support.microsoft.com/kb/246133
You basically run the script to create the sp_help_revlogin procedure and then run sp_help_revlogin to 'script' out the existing user (with original SID and encrypted password)
Now that you have the user's credentials scripted out, you could change the password, let the developer run his job and then run the script to recreate the user with the original username, password and SID.
May 11, 2005 at 1:34 am
Your solution is perfectly feasible, original password will be kept. But the drawback is that it requires dropping user from db and dropping login from the server which requires changing the ownership of db objects, jobs and other entities.
May 11, 2005 at 6:44 am
In the past I've used NGSSQLCrack. I can't remember exactly how it does it, but it has worked for me in the past for situations like you are speaking of.
May 11, 2005 at 7:23 am
Jules,
First of all are you talking about a Windows Login or a SQL Server Login? Simply changing the password of either of these wouldn't stop existing SQL Server Agent jobs from running. A Job is assigned a login as an owner. The job will run under the context of that login if the login has sysadmin rights, if not it is run under the context of the account setup as the SQL Server Agent Proxy account, http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech you never have to apply a password to set an owmer of a job as long as the Login already exists.
Possible solutions could be:
- Create a SQL Server Agent Job with one Transact-SQL Script step containing the script you want to run and is owned by your login that you do not have the password for (if it has sysadmin rights, if not think about granting sysadmin rights temporarily). Scheduling the job should then effectively run it under the context of that Login or the context of the SQL Server Agent Proxy account if the login does not have sysadmin rights .
- Make an identical copy of the account (easy with Windows logins and scriptable with SQL logins) and set the password to something memorable. If you're feeling clever you could then do the following:
Hope this helps,
ll
May 11, 2005 at 7:39 am
Jules,
Actually thinking about it, ignore my second suggested solution i.e. "- Make an identical copy of the account (easy with Windows logins and scriptable with SQL logins) and set the password to something memorable. If you're feeling clever you could then do the following:"
I was thinking along the lines of you running a very simple script that just needs to capture the users login and only updates user database tables and just needs the permissions of the login you don't have the password for. Reading through the article that 'Jpotucek' mentioned above is a far more sensible method if you are going to start moving logins. I still think my first suggested solution could possibly help you out though.
Sorry for any confusion,
ll
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply