December 7, 2011 at 10:31 pm
Where is store sql server user login password..?and what are table used for create syslogin view..?
December 8, 2011 at 12:58 am
sys.syslogins is a view based on three tables, sys.sysxlogins, sys.sysprivs and sys.sysmultiobjrefs. I believe the password is stored in the latter, but I'm not 100% sure. You cannot query any of these tables, although I do fear that someone has found a hack to do it.
December 8, 2011 at 1:47 am
ok fine. i need password change for all the users using query...how to do that..?
December 8, 2011 at 1:53 am
you will have to build a query into like the following to suit the tables, this is just knocked up
SELECT
'ALTER LOGIN '+
user.username+
' WITH PASSWORD = ''£!@123AVeryStrongPasswordGoesHere321@!£''
FROM
sys.sysusers user
This will then bring back one column with the alter login T-SQL for each login in the sys.sysusers table which you can execute by copy and pasting
If you need to do anything else to the login, like set password policy, take a look at the ALTER LOGIN syntax in BOL.
December 8, 2011 at 2:12 am
ya thanks yar.. how to identify my old password..?
December 8, 2011 at 2:15 am
you cant, you can only get the hash value as passwords are encrypted.
one thing you could do is to get the encryption method SQL uses to store password, then pass it in a string and see if you get the same hash code, this is trial and error though.
think the command is pwdencrypt, but maybe a google search will point you in the right direction.
also if the accounts are for applications, I would always make a note of them, if they are users then obivously not and should be using windows authentication where ever possible.
December 8, 2011 at 2:17 am
ok fine..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply