8

Are there any differences between a user in SQL Server and one in Oracle? If so, what are they?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
BeginnerAmongBeginners
  • 279
  • 1
  • 5
  • 12

2 Answers2

12

In Oracle, the users and the schema are one thing. You can create two different tables with the same name, belonging to different users.

In SQL Server, schema and user are separate things. The users are only used to log in and define permissions.

See this question for more information: What is the difference between an Oracle and Microsoft schema?

Tarzan
  • 4,270
  • 8
  • 50
  • 70
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 2
    Additionally, what is a `schema` in Oracle is a `database` in SQL Server. – René Nyffenegger May 11 '10 at 14:57
  • 1
    @René: `SQL Server` has a concept of `schema` too. In the three-part name `'mydb.dbo.mytable'`, `mydb` is a database (physical grouping), while `dbo` is a schema (logical grouping). – Quassnoi May 11 '10 at 15:01
1

Oracle schemas are like My Documents folders in the Windows OS. A user can grant permissions to other users to see things in their schema but an Oracle schema is essentially a user's workspace.

MS SQL Server's schemas are namespaces. While you can have Accounting and Marketing schemas, they are not tightly-coupled to individual users. Objects in an Accounting schema contain accounting information and objects in the Marketing schema have marketing information.

Oracle schemas are tightly-coupled to users and MS SQL Server schemas are primarily for classification.

Tarzan
  • 4,270
  • 8
  • 50
  • 70