Using SQL Developer to connect database via SSH Tunnelling

1. Below is the database server: SID: orcl1, I have normal unix account “donghua” to access the server via ssh.

2. Create the connection string profile. The hostname is “localhost” because the connection is tunnelling via SSH to the server, rather than remotely.

3. Click “Advanced” in above screenshot, enter SSH details. It’s possible to use SSH private key to automate the login. (Not used in my testing)

4. When connect to the database, it will promote for SSH password. (Since I already saved database password, it will not ask DB password here).

5. Connected to database. You can work with the GUI interface rather than SQLPlus now.

How to fix “The database principal owns a schema in the database, and cannot be dropped.”

PS C:\Users\Administrator> sqlcmd -S  .
1> use DB1
2> go
Changed database context to 'DB1'.

1> drop user U1
2> go
Msg 15138, Level 16, State 1, Server WIN-922S55M9QDP, Line 1
The database principal owns a schema in the database, and cannot be dropped.

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------
db_ddladmin
db_datareader
db_datawriter

(3 rows affected)

1> alter authorization on schema::db_ddladmin to dbo
2> go

1> alter authorization on schema::db_datareader to dbo
2> go

1> alter authorization on schema::db_datawriter to dbo
2> go

1> select  name from sys.schemas where principal_id=DATABASE_PRINCIPAL_ID('U1')
2> go
name
----------------------------------------------------

(0 rows affected)
1> drop user u1
2> go