Question Re: User info stored in SQL data tables in Technology Q&A

User info stored in SQL data tables

Former Member — Wed, 18 Nov 2015 16:21:38 GMT

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

Re: User info stored in SQL data tables

yakcinar — Wed, 18 Nov 2015 18:18:51 GMT

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

Re: User info stored in SQL data tables

Former Member — Mon, 25 Jan 2016 00:14:05 GMT

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

Re: User info stored in SQL data tables

anindya_bose — Mon, 25 Jan 2016 01:27:56 GMT

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

Re: User info stored in SQL data tables

Former Member — Sun, 31 Jan 2016 20:25:21 GMT

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

Re: User info stored in SQL data tables

yakcinar — Sun, 31 Jan 2016 20:53:18 GMT

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

Re: User info stored in SQL data tables

Former Member — Tue, 02 Feb 2016 14:54:06 GMT

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.

Deniz

Re: User info stored in SQL data tables

yakcinar — Tue, 02 Feb 2016 15:44:03 GMT

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

Re: User info stored in SQL data tables

Former Member — Thu, 04 Feb 2016 08:21:29 GMT

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

Re: User info stored in SQL data tables

yakcinar — Thu, 04 Feb 2016 08:39:43 GMT

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

Re: User info stored in SQL data tables

Former Member — Thu, 11 Feb 2016 13:13:58 GMT

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

Re: User info stored in SQL data tables

yakcinar — Fri, 12 Feb 2016 14:42:19 GMT

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

Re: User info stored in SQL data tables

Former Member — Fri, 12 Feb 2016 15:36:07 GMT

Yes, thats exactly what i was planning to do.

Thanks a lot!

Deniz

Re: User info stored in SQL data tables

yakcinar — Fri, 12 Feb 2016 16:54:31 GMT

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