Unit 7

Question 1

Which statement do you use to transfer a database object from one schema to another?

Answer 1

ALTER SCHEMA

Question 2

A user who’s granted the EXECUTE object permission can execute what?

Answer 2

a stored procedure or function

Question 3

The highest level at which you can grant permissions is the ________________ level.

Answer 3

server

Question 4

With the ALTER LOGIN statement, you can

Answer 4

enable or disable a login ID

change the name for a login ID

change the default database or language

all of the above

Question 5

Which statement can you use to create a user-defined database role?

Answer 5

CREATE ROLE

Question 6

Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server.

Answer 6

fixed server

Question 7

The entities that can be secured on a server are called

Answer 7

securables

Question 8

To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ________________ mode.

Answer 8

mixed

Question 9

Each of the outcomes listed below is a result of executing the following script except for one. Which one?

CREATE ROLE ExampleRole;
ALTER ROLE db_datareader ADD MEMBER ExampleRole;
GRANT INSERT,UPDATE
   ON Vendors
   TO ExampleRole;
DENY INSERT
  ON Vendors
  TO ASmith;
ALTER ROLE ExampleRole ADD MEMBER ASmith;

By being assigned to the role ExampleRole, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.

The user named ASmith is granted UPDATE permission to the Vendors table and SELECT permission to all user tables.

By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.

The user named ASmith can’t be granted INSERT permission to the Vendors table by being assigned to the role ExampleRole.

Answer 9

By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.

Question 10

Where would you go to create, modify, or delete logins using the Management Studio?

Answer 10

the Security folder for the server

Question 11

Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales?

Answer 11

CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;

Question 12

You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?

Answer 12

all the objects in a database

Question 13

All of the following statements about application roles are true except for one. Which one?

An application role is activated by executing a stored procedure.

Unlike a standard database role, an application role can contain only one member.

Once a connection activates the application role, its security profile changes from that of the login ID to that of the application role.

Once a connection activates an application role, its security profile can only be changed back to that of the login ID if a cookie is created when the role was activated.

Answer 13

Unlike a standard database role, an application role can contain only one member.

Question 14

To log on to SQL Server using your Windows login ID, you use ________________ authentication.

Answer 14

Windows

Question 15

To log on to SQL Server using your SQL Server login ID, you use ________________ authentication.

Answer 15

SQL Server

Question 16

Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?

Answer 16

MUST_CHANGE

Question 17

Before you can delete a user-defined server role, you must

Answer 17

delete all of its members

Question 18

The dbcreator role

Answer 18

is intended for those users who need to be able to work with database objects

lets members create, alter, and drop databases

allows new members to be added to the role

Correct
all of the above

Question 19

The users, groups, logins, and roles that have access to a server are called

Answer 19

principals

Question 20

Which of the following statements creates a database user in the current database from the SQL Server login ID for TomBrown?

Answer 20

CREATE USER TomBrown;

Question 21

Which of the following statements creates a SQL Server login ID for a user named TomBrown with the password ‘abc123XYZ’.

Answer 21

CREATE LOGIN TomBrown WITH PASSWORD = ‘abc123XYZ’;

Question 22

Which statement is true about the DENY and REVOKE statements?

Answer 22

A denied permission can’t be granted by role membership, but a revoked permission can

Question 23

A user who’s granted the REFERENCES object permission can do what?

Answer 23

create objects that refer to the object

Question 24

After you create a schema, you can create any object within that schema by

Answer 24

qualifying the object name with the schema name