User info stored in SQL data tables

[Former Member]

Hello,

I'm pretty new here and i am not sure if this is the right place to ask for a recommendation but here is my question:

I would like to find out where exactly to look for SAP user info (such as user id, password, etc) is stored in SQL data tables.

I know user maintenance is SU01 on SAP side, but need to do SQL side browsing to check and verify if the password is hashed.

Can anybody help me please?

Thank you

[yakcinar · Accepted solution]

Hello Deniz,

Here is the SQL you are looking for;

select MANDT, BNAME, BCODE, PASSCODE, PWDSALTEDHASH, UFLAG from USR02

You can see the detais of fields using SE11 transaction.

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

For some reason it doesn't work.

It comes back saying: Invalid object name 'USR02'

USR02 shows up as a sys.object but i cannot browse the contents of it.

On SAP side i go to SE16 and then USR02 to see all the information about the users.

My question is where to look at on SQL side to see the same information.

Thank you,

Kind Regards,

Deniz

[anindya_bose · Accepted solution]

hi Deniz

Are you trying to access it from HANA SQL editor ?  In that case, add SCHEMA name with USR02.

Like :

select MANDT, BNAME, BCODE, PASSCODE, PWDSALTEDHASH, UFLAG from   "<SAPSCHEMA>"."USR02"

Cheers

Anindya

[Former Member · Accepted solution]

No i am not using HANA sql editor. Its sql 2005.

[yakcinar · Accepted solution]

Hello Deniz,

Can you try sql like this;

select MANDT, BNAME, BCODE, PASSCODE, PWDSALTEDHASH, UFLAG

from [SID].[sid].USR02

Or you can use SQL Management Studio and find USR02 table from table list. Then display the content.

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

Thank you,

I can execute the command but only without PWDSALTEDHASH part.

I get the following error:

Invalid column name PWDSALTEDHASH.

The other columns are displayed when i execute the command without PWDSALTEDHASH.

BR

Deniz

[yakcinar · Accepted solution]

Hello Deniz,

That column came with newer versions. So it is normal.

You can use "select * from [SID].[sid].USR02"  for all columns.

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

Hello Yuksel,

If the column came in newer versions, does that mean in older versions the hashed values (PWDSALTEDHASH) are not listed at all or are they listed under another column ?

I'm using a MS sql server 2005.

Kind Regards,

Deniz

[yakcinar · Accepted solution]

Hello Deniz,

In older versions password of a user were kept in BCODE and PASSCODE columns. There weren't a field for this data.

This PWDSALTEDHASH column came with new versions (could be 2008).

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

Dear Yuksel,

So, how do I display the sha1 hashed value for the password in the older versions?

Is it a combination of both BCODE and PASSCODE columns ?

Or do i have to execute another command to get what i am looking for ?

Cheers,

Deniz

[yakcinar · Accepted solution]

Hello Deniz,

I am not sure about the hash code.

But when I didnot know the pasword of a user, I was copying the BCODE and PASSCODE values of another user (that I knew the password).

Then I could login with the password I knew to the system.

In newer versions PWDSALTEDHASH field also added to these 2.

Regards,

Yuksel AKCINAR

[Former Member · Accepted solution]

Yes, thats exactly what i was planning to do.

Thanks a lot!

BR

Deniz

[yakcinar · Accepted solution]

Hello Deniz,

You can use below sql scripts in SQL Console;

select MANDT, BNAME, BCODE, PASSCODE, PWDSALTEDHASH, UFLAG from [SID].[sid].USR02 where BNAME IN('<Username Password known>', <User name password unknown>)

update [SID].[sid].USR02

set PWDSALTEDHASH = (SELECT PWDSALTEDHASH FROM [SID].[sid].USR02  WHERE BNAME = '<Username Password known>' AND MANDT ='000')

where BNAME ='<User name password unknown>' AND MANDT = '100'

update [SID].[sid].USR02

set PASSCODE = (SELECT PASSCODE FROM [SID].[sid].USR02 WHERE BNAME = '<Username Password known>' AND MANDT ='000')

where BNAME ='<User name password unknown>' AND MANDT = '100'

update [SID].[sid].USR02

set BCODE = (SELECT BCODE FROM [SID].[sid].USR02 WHERE BNAME =  = '<Username Password known>' AND MANDT ='000')

where BNAME ='<User name password unknown>' AND MANDT = '100'

Regards,

Yuksel AKCINAR