Maria Mihailescu

Create a database schema The Schema belongs to a database user, database role, or application role. An object created within a schema has a NULL principal_id in sys.objects and belongs to the Schema owner. Ownership of schema-contained objects is transferable to a database-level principal. The schema owner retains control permission on objects within the Schema. Any new schema belongs to the principal domain. It would help if you created a valid principal domain as the object owner. The system adds the principal domain to the database. Security Permissions - Request to create schema permission on the database. Specify another user as the owner of Schema already created. The caller must have impersonated permission on the user. If the database owner specifies the database role, the caller needs to meet one of the criteria: membership in the role or altering the role's permission. Using SQL Server Management Studio Create a schema - Expand the database folder in Object Explorer Expand the database in which you will create the new database schema Click on the Security folder, point to New, and select Schema. In the Schema-New dialog box, enter a name for the new Schema on the General page, and open the Search Role and User dialog box. Click OK More options The Schema New dialog box offers an option on two additional pages: extended Properties and Permissions. - The permissions page displays a list of all possible securable and the permissions on those securable in the login. The extended properties page allows adding custom properties to database users. Transact SQL Create the Schema - In Object Explorer, you need to connect to an instance of the Database engine. On the standard bar, select New Query. Here is a schema: CREATE SCHEMA Chains; GO CREATE TABLE Chains.Sizes (ChainID int, width dec(10,2)); - - Additional options are executable in a statement. The next example creates a schema named Sprockets, and the owner's name is Annik. The table name is NineProngs. The statement allows Select to Mandar and refuses Select to Prasanna. CREATE SCHEMA Sprockets AUTHORIZATION Annik CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT ON SCHEMA::Sprockets TO Mandar DENY SELECT ON SCHEMA::Sprockets TO Prasanna; GO Execute the following statement if you want to view the schemas in the database: SELECT * FROM sys.schemas;