You are a database administrator on an instance of SQL Server 2008. Your development team is using the Test database. Each developer has a SQL login and is a member of the db_owner fixed database role. By default, all developers use the dbo schema.
Your company recently hired a contract development team to work on a special development project. The contractors need the ability to create new database tables, views, and stored procedures in the Test database. You also want to meet the following requirements:
• Database objects created by the contractors must be separately maintained and easily differentiated from the objects created by the development team.
• Contractors must be able to query and view the definitions of all existing database objects, but not alter them.
• The administrative effort required to manage and secure database objects should be minimized.
Which actions should you take?
A. Create a database schema named Special.
Create a Contractor database role and grant the role SELECT and VIEW DEFINITION permissions on the dbo schema and the CONTROL permission on the Special schema.
Grant the desired CREATE permissions to the Contractor role.
Create a user account for each contractor with the Special schema as the default schema.
Make each contractor a member of the Contractor role.
B. Create a database schema named Special.
Grant the necessary permissions to the Special schema.
C. Create a database role named Contractor and grant the role SELECT and VIEW DEFINITION permissions on the dbo schema and the CONTROL permission on the Special schema.
Create a user account for each contractor.
Make each contractor a member of the Contractor role.
D. Create a DDL trigger that fires each time a contractor creates an object.
Code the trigger to assign the appropriate object permissions.
Correct Answer: A
Explanation/Reference:
You should perform the following actions:
Create a database schema named Special.
Create a Contractor database role and grant the role SELECT and VIEW DEFINITION permissions on the dbo schema and the CONTROL permission on the Special schema.
Grant the desired CREATE permissions to the Contractor role.
Create a user account for each contractor with the Special schema as the default schema.
Make each contractor a member of the Contractor role.
Schemas contain database objects. Using schemas allows you to manage ownership and permissions of database objects more effectively. In this scenario, you should create a separate schema to contain all database objects that contractors create. This will allow you to keep the contractors’ database objects logically separated from the objects created by the development team. The schema name is displayed in Object Manager to make objects easier to identify. You can also grant permissions at the schema level to simplify the management of permissions. If you grant a permission at the schema level, the same permission is implicitly granted for all database objects within the schema, even future objects. You can use the CREATE SCHEMA statement to create a schema, and optionally specify an AUTHORIZATION clause to specify the schema’s owner. The schema’s owner may be a user or role. If no schema owner is specified, dbo is the default schema owner. In this scenario, you could create the Special schema owned by dbo using the following statement:
CREATE SCHEMA Special;
Next, you need to give contractors the ability to perform their necessary tasks. The best way to implement this is to create a database role and grant the role the needed permissions. Then, you can make each contractor a member of the role. Permissions may be granted at the schema level instead of at the object level.
Therefore, you could use the following Transact-SQL to create the Contractor role and grant the role the necessary permissions:
-- Create the Contractor role
CREATE ROLE Contractor;
GO
-- Allows contractors to query and view the definitions of table in the dbo schema
GRANT SELECT, VIEW DEFINITIONON ON SCHEMA::[dbo] to [Contractor];
GO
-- Allows contractors to control Special schema
GRANT CONTROL ON SCHEMA::[Special] to [Contractor];
GO
Next is the really tricky part. When you give CONTROL permission the effect depends on the subject you give the permission - database or schema. CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database. CONTROL on a schema implies the following permissions: TAKE OWNERSHIP ON SCHEMA, VIEW CHANGE TRACKING ON SCHEMA, SELECT ON SCHEMA, INSERT ON SCHEMA, UPDATE ON SCHEMA, DELETE ON SCHEMA, EXECUTE ON SCHEMA, REFERENCES ON SCHEMA, VIEW DEFINITION ON SCHEMA, ALTER ON SCHEMA (see: http://social.technet.microsoft.com/wiki/contents/ articles/11842.sql-server-database-engine-permission-posters.aspx)
In other words, if you have CONTROL permission on a database, you can do whatever you want with all objects in the database, BUT if you have CONTROL permission on a schema you don’t have the same freedom within the schema, because you can not create any object in the schema. To create a schema object (such as a table, a view, a stored procedure and so on) you must have CREATE permission for that object type. This restriction is by design - see: http://connect.microsoft.com/SQLServer/feedback/details/245082/grant-control-to-schema-not-allowing-create-table. That’s why it is necessary to grant explicitly CREATE TABLE, CREATE VIEW, CREATE PROCEDURE permissions:
-- Allows contractors to create tables, views, and stored procedures in the database
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO [Contractor];
GO
After you create the Contractor role, you should then create contractor accounts with the Special schema as a default schema and make them a member of the Contractor role. When a member of the role creates a database object, it will be created in the user’s default schema. The following Transact-SQL will create the Contractor role. When a member of the role creates a database object, it will be created in the user’s default schema. The following Transact-SQL will create the Contractor1 user with a default schema of Special and assign the user to the Contractor role:
CREATE LOGIN [Contractor1] WITH PASSWORD=N’12345′;
GO
CREATE USER [Contractor1] FOR LOGIN [Contractor1] WITH DEFAULT_SCHEMA=[Special];
GO
EXEC sp_addrolemember N’Contractor ‘, N’Contractor1’
--Allow user to connect to database
GRANT CONNECT TO [Contractor1];
GO
You can specify a schema as the user’s default schema before the schema has even been created. 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.
When a schema is no longer needed, such as when the project is complete, you can drop it using the DROP SCHEMA statement. To be dropped, a schema must be empty, or the DROP SCHEMA statement will generate an error. Therefore, in this scenario, you could first move the objects from the Special to the dbo schema.
You can use the TRANSFER clause of the ALTER SCHEMA statement to transfer objects from one schema to another. After transferring all objects to another schema, you could drop the schema. The following Transact-SQL will move the ContractorTbl table from the Special schema to the dbo schema and then drop the Special schema:
ALTER SCHEMA dbo TRANSFER Special.ContractorTbl;
DROP SCHEMA Special;
All of the other options are incorrect because they would not meet the requirements in this scenario.