You are a database administrator on an instance of SQL Server 2008. You create two database schemas using the following Transact-SQL statements:
CREATE SCHEMA Schema1; CREATE SCHEMA Schema2 AUTHORIZATION DevUser;
You create User1 with a default schema of Schema2. Assuming defaults and no other assigned permissions, which statement(s) about user and roles are true?
(Choose all that apply.)
A. User1 can create tables in Schema2.
B. DevUser can drop Schema2 if it contains no database objects.
C. DevUser can drop any objects that have been created in Schema2.
D. DevUser can create database objects in Schema2.
E. DevUser can copy objects in Schema2 to Schema1.
Correct Answer: BC
Explanation/Reference:
In this scenario, you created two schemas using CREATE SCHEMA statements. Each schema has an owner. The CREATE SCHEMA statement can specify an AUTHORIZATION clause to identify the schema’s owner. The schema’s owner may be a user or role. The schema owner automatically has grant permissions on any objects in the schema and has permission to drop the schema if it is empty. By default, the schema owner cannot create any objects, so additional permissions must be granted to allow this if necessary. However, by default, the schema owner can drop any objects in the schema.
In this scenario, you first created a schema named Schema1 without specifying a schema owner. If no owner is specified, dbo will be the schema’s default owner.
Next, you created a schema named Schema2 specifying DevUser as the schema’s owner. Because DevUser was specified as the owner of Schema2, DevUser can drop Schema2 if it contains no database objects and can drop any objects created in Schema2.
Finally, you created User1 with a default schema of Schema2. When creating or altering a user, you can specify the user’s default schema using the DEFAULT_SCHEMA clause. If you omit the default schema, dbo will be used as the default schema. If the user subsequently creates a database object without specifying a schema, the object will be created in the user’s default schema. In this scenario, if User1 created an object, it would be created in the Schema2 schema by default. You should note that if the user is authenticated via a Windows group, the user will have no default schema assigned. If such a user creates an object, SQL Server will create a new schema in which the object is created. The new schema created will have the same name as the user that created the object.
You should always specify a schema when creating a user.
Schemas contain database objects. Using schemas allows you to manage ownership and permissions on database objects more effectively because the schema and its ownership are separate from the user. Schemas allow you to organize objects and easily grant and revoke permissions on groups of objects. When you grant a user an object permission at the schema level, the permission is automatically given to the user for all objects within the schema. In addition, the permission is also implicitly granted on any future objects created in the schema without any further action. Because users and object ownership are separate, you can drop a user that owns objects without having to first transfer ownership of the objects to another user.
CREATE USER DevUser FOR LOGIN DevUser
GO
CREATE SCHEMA Schema2 AUTHORIZATION DevUser;
GO
CREATE USER User1 FOR LOGIN User1 WITH DEFAULT_SCHEMA=Schema2
GO
EXECUTE AS USER=’User1′
SELECT CURRENT_USER --return User1
--Try to create a table T in Schema2
CREATE TABLE Schema2.T (ID int)
GO
/*
Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database …
*/
--So User1 cannot create a table in Schema2 even though it is his default schema
REVERT
EXECUTE AS USER=’DevUser’
SELECT CURRENT_USER --return DevUser
--Try to create a table T in Schema2
CREATE TABLE Schema2.T (ID int)
GO
/*
Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database …
*/
--So DevUser cannot create a table in Schema2 even though he is the owner of this schema
REVERT
SELECT CURRENT_USER --return dbo
CREATE TABLE Schema2.T (ID int)
GO
/*
Command(s) completed successfully.
*/
EXECUTE AS USER=’DevUser’
SELECT CURRENT_USER --return DevUser
--Try to drop the table T in Schema2
DROP Table Schema2.T
GO
--So DevUser can drop a table in Schema2
/*
Command(s) completed successfully.
*/
--Try to drop empty Schema2
DROP Schema Schema2
GO
/*
Command(s) completed successfully.
*/
DevUser cannot copy objects in Schema2 to Schema1 because DevUser is neither the schema owner nor been granted permission at the schema level. In this scenario, you created Schema1 without specifying an owner, so the owner will default to dbo.