What is the importance of schema in sql server?

sql-server schema
masoud ramezani picture masoud ramezani · Feb 8, 2010 · Viewed 20.2k times · Source

What is the importance of schema in sql server?

Where this schema help me? Is it important for security reasons?

Answer

mjv picture mjv · Feb 8, 2010

Yes, the primary purpose of SQL schema was -is- to facilitate security management: define who [which principals] can access what [which database objects]. This was made particularly easier starting with SQL 2005 when the schema stopped being directly tied to the owner.

Another use of schema is to serve as a namespace, that is preventing name clashes between objects from different schemas.
The original use of this was to allow multiple [interactive, i.e. ad-hoc like] users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users.
The Namespace-like nature of schema can also be put to use in a planned database setting, i.e. one when a single architect designs the database structure in a way which provides distinct type of access, and indeed different behaviors, for distinct user groups.

Related questions

Change Schema Name Of Table In SQL

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ? Example: FROM dbo.Employees TO exe.Employees …

Read More
How do I change db schema to dbo

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I …

Read More
How do I move a table into a schema in T-SQL

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.

Read More