Exam DP-200 All Questions

View all questions & answers for the DP-200 exam

Exam DP-200 topic 3 question 31 discussion

Actual exam question from Microsoft's DP-200

Question #: 31
Topic #: 3

You plan to use Microsoft Azure SQL Database instances with strict user access control. A user object must:
✑ Move with the database if it is run elsewhere
✑ Be able to create additional users
You need to create the user object with correct permissions.
Which two Transact-SQL commands should you run? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. ALTER LOGIN Mary WITH PASSWORD = 'strong_password';
B. CREATE LOGIN Mary WITH PASSWORD = 'strong_password';
C. ALTER ROLE db_owner ADD MEMBER Mary;
D. CREATE USER Mary WITH PASSWORD = 'strong_password';
E. GRANT ALTER ANY USER TO Mary;

Show Suggested Answer

Suggested Answer: CD 🗳️
C: ALTER ROLE adds or removes members to or from a database role, or changes the name of a user-defined database role.
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL
Server.
D: CREATE USER adds a user to the current database.
Note: Logins are created at the server level, while users are created at the database level. In other words, a login allows you to connect to the SQL Server service
(also called an instance), and permissions inside the database are granted to the database users, not the logins. The logins will be assigned to server roles (for example, serveradmin) and the database users will be assigned to roles within that database (eg. db_datareader, db_bckupoperator).
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql

by Maeklund86 at Feb. 21, 2020, 7:30 a.m.

Comments

Submit Cancel

pingvins11

Highly Voted 5 years, 4 months ago

The correct answer is: D: CREATE USER Mary WITH PASSWORD = 'strong_password'; E: GRANT ALTER ANY USER TO Mary; Initially, only one of the administrators or the owner of the database can create users. To authorize additional users to create new users, grant that selected user the ALTER ANY USER permission, by using a statement such as: GRANT ALTER ANY USER TO Mary; https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

upvoted 37 times

Leonido

5 years, 2 months ago

And user will be contained since defined in the database, not server or master.

upvoted 1 times

...

oku

5 years ago

Creating a User by this , User can not Move with the database if it is run elsewhere , so it should be In Admin Role

upvoted 7 times

...

emski

4 years, 1 month ago

D & E are correct. see "USER | ALTER | AL | DATABASE | ALTER ANY USER" https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15#_permissions

upvoted 1 times

...

Ola

Highly Voted 4 years, 5 months ago

The answers are correct. D -CREATE USER adds a user to the current database. - because need to create user at the database level. C - ALTER ROLE db_owner ADD MEMBER Mary - need to add Mary on db level https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15 ( E is not corect because it works on server level https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver15)

upvoted 7 times

Internet_User

4 years, 2 months ago

This response is not correct. ALTER ANY USER is a database-level permission, and allows the user to CREATE USER. See the PDF Chart of SQL Permissions and scroll to the far right, under "Connect and Authentication - Database Permissions" at https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15 Making Mary db_owner is giving the user way more permission than necessary for the requirement of just adding users.

upvoted 2 times

...

Aakansha01

Most Recent 4 years, 1 month ago

C & D are correct, from udemy course.

upvoted 1 times

...

111222333

4 years, 1 month ago

D & E https://stackoverflow.com/questions/27526763/grant-permission-to-create-a-user-for-any-database

upvoted 1 times

...

Raya2021

4 years, 1 month ago

D or E? D or C?

upvoted 1 times

...

cadio30

4 years, 2 months ago

C & D are correct. The option E can only create and drop users and didn't meet the requirement whenever the database is "move". Reference: https://docs.microsoft.com/en-us/sql/analytics-platform-system/grant-permissions?view=aps-pdw-2016-au7

upvoted 1 times

...

JohnCrawford

4 years, 6 months ago

The user object must move with the database...in other words you need a contained user. To create a contained user you run a CREATE USER statement. There is no login involved. Logins are server level and stored in master. The question also states that new users will need to be created. The answer that meets that requirement is giving the GRANT ALTER ANY USER permission.

upvoted 1 times

...

rajat009

4 years, 7 months ago

1.Mary is added to dbo role . Hence she can move along whereever db moves meeting #1 req. 2. She is a dbo hence can add new users, just need her login. Answers looks correct to me

upvoted 2 times

...

cowtown

5 years ago

https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver15#contained-database-user-model

upvoted 2 times

...

Maeklund86

5 years, 4 months ago

This is really bad practice. There is a specific role for handling logins and users on a database level, db_accessadmin. Giving owner-role for this task is NOT a good idea.

upvoted 3 times

Leonido

5 years, 2 months ago

It doesn't satisfy "strict user access control"

upvoted 2 times

...