SQL Server 2005 Schema, Schema Properties, Schema T-SQL
Posted by Jugal Shah on January 3, 2009
What is Schema in SQL Server 2005? Explain its properties with example?
A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.
Properties
- Ownership of schemas and schema-scoped securables is transferable.
- Objects can be moved between schemas
- A single schema can contain objects owned by multiple database users.
- Multiple database users can share a single default schema.
- Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.
- A schema can be owned by any database principal. This includes roles and application roles.
- A database user can be dropped without dropping objects in a corresponding schema.
Create database SQL2k5
Use SQL2k5
– Created Schema Employee –
Create Schema Employee
– Created table in Employee schema –
Create Table Employee.EmpInfo
(
EmpNo int Primary Key identity(1,1),
EmpName varchar(20)
)
– data insertion –
Insert Into Employee.Empinfo Values(‘Jshah-3′)
– Data Selection –
Select * From Employee.Empinfo
– Created another schema HR –
Create Schema HR
– Transfer Objects between Schemas –
ALTER SCHEMA HR
TRANSFER Employee.Empinfo
– Assigning Permission to Schema –
GRANT SELECT ON SCHEMA::HR TO Jshah
anil said
Can I have a decent web chat in ASp.Net C# which I can implemented on my site. I get lot of open source web chat and not a single one in Asp.Net.
Sergio said
А если посмотреть на это с другой точки зрения то не все так гладко получается
abhilash said
but how can we change default schema for a particular user
Ramu said
Good Explanation with examples.
Nalin said
hi this is usefull notes for Know so u have good job man
farengayts said
сёдня наконец-то пришёл конвертик от space-shop.org! всё без палева и чётко! буду и дальше у вас заказывать!