41.
You want to create an instance-level security principal named Development on server
SQL-A. This security principal should use the password Pa$$w0rd and should be
authenticated by the SQL Server 2012 instance. Which of the following Transact-SQL
statements would you use to accomplish this goal?
- A.CREATE LOGIN “SQL-A\Development” FROM WINDOWS
- B.CREATE USER “SQL-A\Development”
- C.CREATE LOGIN [Development] WITH PASSWORD = ‘Pa$$w0rd’
- D.CREATE USER [Development] WITH PASSWORD = ‘Pa$$word’
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
|
A. Incorrect: You should not execute the CREATE LOGIN “SQL-A\Development”
FROM WINDOWS statement. This statement will create a Windows-authenticated
login based on a local account named Development. B. Incorrect: You should not execute the CREATE USER “SQL-A\Development” statement. This statement creates a contained user based on a local account named Development. C. Correct: You should execute the statement “CREATE LOGIN [Development] WITH PASSWORD = ‘Pa$$w0rd’” because this will create a SQL Server–authenticated login named Development that uses the password Pa$$w0rd. D. Incorrect: You should not execute the CREATE USER [Development] WITH PASSWORD = ‘Pa$$word’ statement because it creates a contained database user named Development that uses SQL authentication rather than a SQL Server login that users SQL authentication. |
42.
Following the principle of least privilege, to which of the following fixed server-level
roles would you add a SQL Server login if you wanted the user to be able to drop and
restore databases?
- A.sysadmin
- B.securityadmin
- C.setupadmin
- D.dbcreator
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
|
A. Incorrect: You would not add the SQL login to the sysadmin role. This role enables
members to perform all activities possible on the Database Engine instance. B. Incorrect: You would not add the SQL login to the securityadmin fixed server role. This role enables management of instance-level permissions. C. Incorrect: You would not add the SQL login to the setupadmin fixed server role. This role allows for the adding and removing of linked servers. D. Correct: You would assign the dbcreator role. This allows the SQL Server login to perform tasks of dropping and restoring databases without assigning unnecessary permissions. |
43.
You have created a database instance security principal that maps to a domain-based
user account. Which of the following should you create at the database level so that
you can grant the database instance security principal the appropriate privileges to
create and drop tables in the database?
- A.Login
- B.Server role
- C.Credential
- D.User
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
|
A. Incorrect: Logins are Database Engine instance–level security principals. You need
to create a user mapped to the existing login to accomplish your goal. B. Incorrect: Server roles are Database Engine–level security principals. You need to create a user mapped to the existing login to accomplish your goal. C. Incorrect: Credentials allow the Database Engine access to external resources that require authentication. You need to create a user mapped to the existing login to accomplish your goal. D. Correct: You can create database users that are mapped to SQL logins when you want to assign permissions at the database level. You can also create flexible database roles, add the database user to the role, and assign the appropriate permissions to the role. |
44.
You want to create a security principal at the database level, add existing database
users to this security principal, and assign permissions to it. Which of the following
Transact-SQL statements would you use to accomplish this goal?
- A.CREATE ROLE
- B.CREATE SERVER ROLE
- C.ALTER ROLE
- D.ALTER SERVER ROLE
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
|
A. Correct: You use the CREATE ROLE statement to create a flexible database role.
You can add existing database users to a flexible database role and assign permissions
to that role. B. Incorrect: You should not use the CREATE SERVER ROLE statement because this creates a user-defined server role at the instance rather than at the database level. C. Incorrect: You use the ALTER ROLE statement to rename an existing flexible server role. D. Incorrect: You use the ALTER SERVER ROLE statement to alter user-defined server roles at the instance rather than at the database level. |
45.
Which of the following statements or stored procedures do you use to add database
users to a fixed database role?
- A.ALTER SERVER ROLE
- B.ALTER ROLE
- C.sp_addrolemember
- D.CREATE ROLE
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
|
A. Incorrect: The ALTER SERVER ROLE statement enables you to add security principals
at the instance level to a user-defined server role rather than to a flexible
database role. B. Incorrect: The ALTER ROLE statement enables you to change the name of a flexible database role but does not enable you to alter the membership of a flexible database role. C. Correct: You use the sp_addrolemember stored procedure to add members to a flexible database role. D. Incorrect: You use the CREATE ROLE statement to create flexible database roles. You can’t use this statement to modify the membership of a flexible database role. |
|
|
||||||||||||||||||||||||||||
