ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-200 All Questions

View all questions & answers for the DP-200 exam
Go to Exam

Exam DP-200 topic 3 question 31 discussion

Actual exam question from Microsoft's DP-200
Question #: 31
Topic #: 3
[All DP-200 Questions]

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 Hide 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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
pingvins11
Highly Voted 5 years, 2 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 ago
And user will be contained since defined in the database, not server or master.
upvoted 1 times
...
oku
4 years, 10 months 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
3 years, 11 months 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, 3 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 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 3 years, 11 months ago
C & D are correct, from udemy course.
upvoted 1 times
...
111222333
3 years, 11 months ago
D & E https://stackoverflow.com/questions/27526763/grant-permission-to-create-a-user-for-any-database
upvoted 1 times
...
Raya2021
3 years, 12 months ago
D or E? D or C?
upvoted 1 times
...
cadio30
4 years 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, 4 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, 5 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
4 years, 10 months 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, 2 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 ago
It doesn't satisfy "strict user access control"
upvoted 2 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago