Tuesday, January 13, 2015

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.

clip_image002

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

clip_image003

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)

clip_image004

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).

clip_image005

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

clip_image007

Friday, January 2, 2015

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