Which actions should you take?

Correct Answer for the following Question is given below

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?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.Grant the necessary permissions to the Special schema.Create a user account for each contractor.Make each contractor a member of the Contractor role.Code the trigger to assign the appropriate object permissions.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 roleCREATE ROLE Contractor;GO– Allows contractors to query and view the definitions of table in the dbo schemaGRANT SELECT, VIEW DEFINITIONON ON SCHEMA::[dbo] to [Contractor];GO– Allows contractors to control Special schemaGRANT CONTROL ON SCHEMA::[Special] to [Contractor];GONext 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 databaseGRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO [Contractor];GOAfter 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′;GOCREATE USER [Contractor1] FOR LOGIN [Contractor1] WITH DEFAULT_SCHEMA=[Special];GOEXEC sp_addrolemember N’Contractor ‘, N’Contractor1’–Allow user to connect to databaseGRANT CONNECT TO [Contractor1];GOYou 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.

Question:

Which actions should you take?

Options:

Create a database schema named Special.

Create a database schema named Special.

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 DDL trigger that fires each time a contractor creates an object.

Correct Answer

The Correct Answer for this Question is

Create a database schema named Special.

Explanation

The Question – Which actions should you take? has been answered correctly and answers for the question is Create a database schema named Special.

More about these Exams

These Exam Questions and the order of these questions keep changing. but the answers are obviously same. so if you don’t find a question after another we suggest you search it in the search box and we are sure you’ll find it. you can bookmark this site for Quick access in future.

We hope you found it helpful don’t forget to leave a comment if you feel a need to correct or ask we’re always here to help.

you can find more here at mnccertified

Feel free to contact via comment or email.

Happy Learning

Cheers, Team MNCcertified