DATA BASE TEST02

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

ALTER SCHEMA

_____ data masking is a feature that is used to protect sensitive data from being presented to end users in an application. This is something that can be done in the application layer, that is being done in the database.

Dynamic

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

MUST_CHANGE

How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?

ON DATABASE

Which keyword can you use to pass a parameter from a stored procedure back to the calling program?

OUTPUT

Which of the following is the default transaction isolation level for SQL Server?

READ COMMITTED

Which of the following statements returns the value of a variable named @InvoiceCount?

RETURN @InvoiceCount;

Stored procedures execute faster than an equivalent SQL script because stored procedures are what?

precompiled

Row level security is based on a _____ function, and it limits the rows, which are returned to the user, regardless of which application they're using.

predicate

Data validation is the process of

preventing errors due to invalid data

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

principals

Dynamic data masking is a presentation-layer security feature, and what that means is that data is stored in an unmasked format in the database, so don't confuse this with encryption. It's not a full-on security feature, but it is a _____ feature.

privacy

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

qualifying the object name with the schema name

Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the

schema name

The entities that can be secured on a server are called

securables

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

server

Row level security allows you to configure_____, so that users can only see the rows that they have been granted access to.

tables

SQL Server inserts data into the _____ database before data is written to disk.

tempdb

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

the Security folder for the server

What is lock promotion?

the conversion of a less exclusive lock to a more exclusive lock

What is lock escalation?

the conversion of several finer-grained locks to a single coarse-grain lock

When you commit a transaction,

the operations performed by the SQL statements become a permanent part of the database

If you don't group statements into explicit transactions, SQL Server automatically treats each SQL statement as a separate what?

transaction

Encryption in _____ -- means that data going across the network is encrypted. By default this isn't the case with SQL server. It is the case with Azure SQL Database, all that traffic is encrypted.

transit

A lost update occurs when

two transactions select the same row and then update the row based on the values originally selected

By default, SQL Server is in autocommit mode. This means

unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction

If you delete a stored procedure, function, or trigger and then create it again

you delete the security permissions assigned to the object

Note about dynamic data masking: administrative users will always have full access to the complete data. For data sets that have the requirement that administrators shouldn't have access to the data, you should be using _____ Encrypted.

Always

SQL Server's lock manager always tries to lock resources

at the highest possible granularity

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

all the objects in a database

Which system function can you use to determine how many levels deep transactions are nested?

@@TRANCOUNT

Which statement is true about the DENY and REVOKE statements?

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

Each of the following statements about triggers is true except for one. Which one?

A trigger can have more than one batch.

Which statement can you use to explicitly start a transaction?

BEGIN TRAN

_____ predicates prevent the user from writing data so you can do after insert, after update or before update and before delete

Block

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,UPDATEON VendorsTO ExampleRole;DENY INSERTON VendorsTO ASmith;ALTER ROLE ExampleRole ADD MEMBER ASmith;

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.

You can invoke a table-valued user-defined function

anywhere you'd refer to a table or a view

Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?

CREATE

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

CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';

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

CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;

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

CREATE ROLE

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

CREATE USER TomBrown;

If you want to prevent users from examining the SQL code that defines a procedure, function, or trigger, you code the CREATE statement with the ________________ option

ENCRYPTION

Which of the following statements executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount?(Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn't accept any parameters.)

EXEC @InvoiceCount = spInvoiceCount;

Which of the following statements calls the stored procedure and passes the values '2019-10-01' and 122 to its input parameters?CREATE PROC spInvoiceTotal1@DateVar smalldatetime,@VendorID intASSELECT SUM(InvoiceTotal)FROM InvoicesWHERE VendorID = @VendorID AND InvoiceDate >= @DateVar;______________________________________________

EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2019-10-01';

Which of the following statements calls the following stored procedure, passes the value '2019-10-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal?CREATE PROC spInvoiceTotal2@DateVar smalldatetime,@InvoiceTotal money OUTPUTASSELECT @InvoiceTotal = SUM(InvoiceTotal)FROM InvoicesWHERE InvoiceDate >= @DateVar;(Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)

EXEC spInvoiceTotal2 '2019-10-01', @MyInvoiceTotal OUTPUT;

_____ always comes with a trade-off 'cause there are CPU costs associated with this process.

Encryption

How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?

INSTEAD OF DROP_VIEW

Which of the following is the most restrictive transaction isolation level?

SERIALIZABLE

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

SQL Server

_____ layer encryption -- means if somebody steals a hard disk or hacks into your Azure account and gets a copy of your database file, they're not gonna be able to get at it without having the certificate or the key.

Storage

If you're using_____, don't think your sensitive data is encrypted. Just know that it's only encrypted at rest and users can still query that data.

TDE

Which statement can you use to manually raise an error within a stored procedure?

THROW

In order to enable encryption at transit for SQL server you need _____ certificates in your environment, on both the client and the server.

TLS

Each of the following is a valid reason to explicitly code a transaction except for one. Which one?

The results of a SELECT query will be used as a subquery.

_____ data encryption and encryption at rest -- simply just encrypts your data files and your backups. So what this means is if you shut down your SQL Server and somebody copied the MDF and LDF files that were behind your SQL Server and tried to attach them to another server, they couldn't do it without the certificate.

Transparent

A mask also can't be configured on a computed column, but if a computed column depends on a column with the mask, then the computed column will return masked data

True

Dynamic data masking is not a replacement for encryption.

True

If unprivileged users have ad hoc query permissions, they can apply techniques to gain access to the actual data.

True

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

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

Each of the following techniques can help to prevent deadlocks except for one. Which one?

Use the highest transaction isolation level possible.

Row-level security filters, predicates, that are functionally equivalent to appending a _____clause to the query

WHERE

The dbcreator role

all of the above

When you use Transact-SQL, you can store procedural code in

all of the above

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

Windows

Which of the following is true about a transaction?

all of the above

With the ALTER LOGIN statement, you can

all of the above

If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it's called

a deadlock

To make a parameter for a stored procedure optional, what do you assign to it?

a default value

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

a stored procedure or function

A dirty read occurs when

a transaction selects data that isn't committed by another transaction

Concurrency is

all of the above

System stored procedures

all of the above

There are two types of predicates used for row-level security. There's filter rows, which are going to silently filter the rows that are available for reading, and then _____ predicates, which are going to explicitly stop write operations, like insert, update, or delete, that violate the predicate, and the predicate is restricted by an inline table valued function that you've invoked and enforced by a security policy.

block

A user-defined function

can return a single scalar value or a single table value

If you've declared save points, the COMMIT TRAN statement

commits the entire transaction

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

create objects that refer to the object

If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement

decrements @@TRANCOUNT and commits the transaction

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

delete all of its members

What kind of lock only allows one transaction to access a resource?

exclusive

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

fixed server

The relative amount of data that's included in a resource can be referred to as the resource's

granularity

You typically use the return value of a stored procedure to

indicate to the calling program whether the stored procedure completed successfully

Which of the following is not one of the four types of concurrency problems?

integrity reads

Which lock indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grain resource?

intent lock

One of the drawbacks of using the SERIALIZABLE isolation level is

it can cause severe performance problems

What does SQL Server automatically use to avoid some concurrency problems?

locks

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

mixed

For each type of action query, a table can have

multiple AFTER triggers and one INSTEAD OF trigger

When passing a list of parameters to a stored procedure by name, you can omit optional parameters by

omitting the parameter name and value from the list