Exam 70-764 topic 1 question 114 discussion

Actual exam question from Microsoft's 70-764

Question #: 114
Topic #: 1

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2016.
You attempt to add the [Corpnet\User1] login to the database.
However, you receive the following error message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions.
You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

A. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];
B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];

Show Suggested Answer

Suggested Answer: C 🗳️

by tomzus at Dec. 7, 2019, 1:23 p.m.

Comments

Submit Cancel

huzein

4 years, 8 months ago

After migrating or restoring a database the users on the restored or migrated database are orphaned because of different internal SID's. In order to fix that issue, you can do as shown in C. Alternatively you can also use the sproc sys.sp_change_users_login, but that is not listed here.

upvoted 1 times

...

norbert1

4 years, 9 months ago

might anyone explain what happens here and why this is the solution..? why does alter with login work - and why is it necessary..?

upvoted 1 times

Hoglet

4 years, 5 months ago

First off, it isn’t necessary in this case. If you’re using Windows Logins the AD guid is a function of the login Sid. So when you move databases between servers the relation is still correct if the login works. This isn’t true for SQL Logins, the Sid will be different between servers, so when you move the db and recreate the login, it is a different login, the Sid is different

upvoted 1 times

Hoglet

4 years, 5 months ago

As to why this is the solution, the command updates the relationship between the login and the User. So where the User was linked to a login which might not exist on the server, it is now connected to an existing login. We maintain different windows accounts for PRD and UAT. So when were restore the PRD to UAT, run commands such as ALTER USER [myDomain\AppServiceAccountPRD] WITH [myDomain\AppServiceAccountUAT] Hope that makes sense

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

Tie the database user to the login via the ALTER USER command.

upvoted 1 times

...

tomzus

5 years, 6 months ago

Answer missing equal sign alter user [Corpnet\User1] with login = [Corpnet\User1]

upvoted 4 times

...