Database Reference
In-Depth Information
While authentication controls users' access to SQL Database, authorization controls per-
missions to users to access certain database objects and perform contain tasks. In Windows
Azure SQL Database, authorization can be implemented through the coniguration of users,
roles, schemas, and related login properties.
ACTIVITY 12.2 MANAGING SQL DATABASE WITH SCHEMAS
As mentioned earlier, schemas are used to manage database objects and security. A schema
can be created by using the command CREATE SCHEMA. To illustrate the creation and
use of schemas, let us go through the following steps:
1. In
Internet Explorer
, enter the following URL to the SQL Database Management
Portal:
https://qmcooqcanr.database.windows.net/?langid
=
en-us#$database
=
Class_
Registration
Again, you need to change qmcooqcanr to your database server's name.
2. Log on to SQL Database with your user name and password.
3. To create a schema account and add a table ACCOUNT to it, use the following SQL
statement in the
New Query
window:
CREATE SCHEMA Accounting
CREATE TABLE ACCOUNT (AccountID INT, Balance MONEY)
4. You can assign a user with the default schema. For example, the following SQL
statement will assign the user Smith with the default schema Accounting and grant
Smith the permission to use the ALTER statement. To do so, run the following SQL
statement:
ALTER USER Smith WITH DEFAULT_SCHEMA
=
Accounting
5. hen, run the following SQL statement to create an account for Smith in the
Class_Registration database and grant the permission for Smith to alter the schema
Accounting.
GRANT ALTER ON SCHEMA::Accounting TO Smith
When Smith logs on to the Class_Registration database, the default schema for Smith
is Accounting. Smith will be the owner of the ACCOUNT table.
6. A fully qualiied database object name has the format that contains four parts server.
database.schema.object. When missing the names of the server and database, a data-
base object takes the current server and database as default. When missing the name
