cancel
Showing results for 
Search instead for 
Did you mean: 

How to delete user SAP* from database table in MSSQL 2005 server

Former Member
0 Kudos

Respected Guru's,

we are unable to access user SAP* as the password is not available.

I need to delete user SAP* from database table and restart the server using MSSQL 2005 server.

Please help me to do so, as i am a novice in MSSQL 2005 server.

Regards,

Daya

Accepted Solutions (1)

Accepted Solutions (1)

markus_doehr2
Active Contributor
0 Kudos

The command would be

delete from usr02 where bname = 'SAP*' and mandt = <client-number>

After that you need to make sure the system as the profile parameter

login/no_automatic_user_sapstar = 0

set, restart the instance and logon with SAP*.

Markus

Former Member
0 Kudos

Thank you,

But i want to do it from MSSQL 2005 Server, kindly let me know the step in doing this.

Should i execute this command in Management studio or some where else.

Regards,

Daya

markus_doehr2
Active Contributor
0 Kudos

> But i want to do it from MSSQL 2005 Server, kindly let me know the step in doing this.

This is the command for SQL Server...

> Should i execute this command in Management studio or some where else.

Yes. Open a query window for the database and execute that command.

Markus

Former Member
0 Kudos

Thank you Markus,

when i executed the below mentioned command

delete from usr02 where bname = 'SAP*' and mandt = '500'

i get the follwoing error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'usr02'.

i have also tried giving the client no without ' ' but i get the same error.

Please help me

Regards

Daya.

anindya_bose
Active Contributor
0 Kudos

add your SAP Schema name

delete from SAPDAT.usr02 where bname = 'SAP*' and mandt = '500'

Check your SAP Chema name. It might not be SAPDAT

Former Member
0 Kudos

Anindya,

please let me know how to find the schema name of the database.

Regards,

Daya.

Former Member
0 Kudos

I mean the schema for table usr02

anindya_bose
Active Contributor
0 Kudos

Login to SAP system , from teh initial screen, SYSTEM--->Status

Check the database owner name in the lower right part

Former Member
0 Kudos

Anindya,

i have executed the following command as my database owner name is 'alt'

delete from SAPALT.usr02 where bname = 'SAP*' and mandt = '300'

but i get the following error,

Msg 208, Level 16, State 1, Line 1
Invalid object name 'SAPALT.usr02'.

Please help me,

Daya.

Former Member
0 Kudos

Try this:

/* I asume ALT is name of your DB*/

use ALT

go

setuser 'alt'

go

delete from usr02 where bname = 'SAP*' and mandt = '300'

Sven

anindya_bose
Active Contributor
0 Kudos

Try this ..note the double quote

>delete from SAPALT."usr02" where bname = 'SAP*' and mandt = '300';

can you access USR02 table from SAP system ( SE16 or SE11).

Can you try a command

>select username from dba_users;

Former Member
0 Kudos

Thank you Anindya,

finnally out of your guidence i have reset the password for SAP* password as PASS.

problem was with the case that i have entered. Client and username field should be in caps and schema name should be as it is in system status.

But, when i checked in SU01 user SAP* does not exsist, but i am able to logon.

What's happening?

Regards,

Dayanandan.

anindya_bose
Active Contributor
0 Kudos

This is expected.

Currently you are using SAP* with hardcoded password "pass" from the kernel.

If you want to use your own password then you have to create the user master from su01 for SAP. Once you create user master for SAP and change the password the default password "pass" will be overwritten.

Presently there is no user master record for sap*

markus_doehr2
Active Contributor
0 Kudos

>

But, when i checked in SU01 user SAP* does not exsist, but i am able to logon.

> What's happening?

This is expected behaviour.

SAP* is not a real "user", it's hardcoded into the kernel.

Once you log in with that user an SU01 entry will be created to be able to set a password for the user. But it's not a real user and should not be used for administration tasks.

Since that was a security issue with older version; so you should set the parameter login/no_automatic_user_sapstar back to 1 once you're logged in.

Markus

Former Member
0 Kudos

Anindya and Markus,

Thanks to both of them, for helping me to complete the work by sharing knwoledge.

I have recreated SAP* with new password.

Regards,

Daya.

Answers (1)

Answers (1)

Former Member
0 Kudos

This message was moderated.

markus_doehr2
Active Contributor
0 Kudos

> How to delete user SAP* from database table in oracle 9.0

- please read the full thread, the statement is posted there

- use the search function in the right upper corner and enter "delete sap*"

Markus