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

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam
Go to Exam

Exam 70-764 topic 1 question 45 discussion

Actual exam question from Microsoft's 70-764
Question #: 45
Topic #: 1
[All 70-764 Questions]

DRAG DROP -
You administer a Microsoft SQL Server database named Contoso. You create a stored procedure named Sales.ReviewInvoice by running the following Transact-
SQL statement:

You need to create a Windows-authenticated login named ContosoSearch and ensure that ContosoSearch can run the Sales.ReviewInvoices stored procedure.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-
SQL segments to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer Hide Answer
Suggested Answer:
by RohitM at Sept. 23, 2019, 10:14 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
mrn0107
Highly Voted 5 years, 5 months ago
The most of this answers are wrong. If you take windows login, like it said, then you do not need password. The third part of the question is also wrong. You need to grant execute rights on SP and select rights on table.
upvoted 7 times
mickeyisacat615
4 years, 7 months ago
Agree with you!
upvoted 1 times
...
...
msamm
Highly Voted 5 years, 8 months ago
The Third statement should be Grant Execute on Sales.ReviewInvoice Grant Select on Sales.SalesInvoice. The user would need to have select permission to the table in order to obtain data.
upvoted 7 times
...
huzein
Most Recent 4 years, 8 months ago
Jkilleen is right. Box 2 is also wrong. Should be "...create user..for login ...\ContosoSearch".
upvoted 1 times
...
Jkilleen
4 years, 9 months ago
What has Contoso\SalesGroup got to do with anything. It doesnt say that Contoso\ContosoSearch is a member of that group and it doesnt require execute/select permissions for SalesGroup.
upvoted 1 times
...
jolsca
4 years, 9 months ago
1. Create Login from windows 2. Create user for login 3. Grant Execute on Sales.ReviewInvoice Grant Select on Sales.SalesInvoice.
upvoted 3 times
...
MSSucks
5 years, 4 months ago
mrn0107 is correct. 1. Box 1 should be "CREATE LOGIN ... FROM WINDOWS". 2. Execute permission on the SP *and* select permission on the table is neccessary. Because dynamic SQL is involved (sp_executesql) which breaks owner chaining. https://www.mssqltips.com/sqlservertip/1822/dynamic-sql-and-ownership-chaining-in-sql-server/ This article is from 2009 but I just tested it myself on a SQL Server 2017.
upvoted 2 times
Hoglet
4 years, 5 months ago
This one!!!! If the SP wasn’t using dynamic SQL, then only execute on the SP would be required. If the SP had the option EXECUTE AS OWNER, when the dynamic sql executed it would still be under the same security context as the SP owner/creator and only execute on the SP would be required. As we don’t have that option set, the dynamic sql runs as the caller and do he requires SELECT on that table
upvoted 1 times
...
...
plyti6ka
5 years, 4 months ago
Encapsulating data access through modules such as stored procedures and user-defined functions provides an additional layer of protection around your application. You can prevent users from directly interacting with database objects by granting permissions only to stored procedures or functions while denying permissions to underlying objects such as tables. SQL Server achieves this by ownership chaining. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authorization-and-permissions-in-sql-server
upvoted 1 times
...
shb
5 years, 4 months ago
" mrn0107"'s answer is correct
upvoted 1 times
...
tomzus
5 years, 7 months ago
also should have added you can create a login for an individual windows account or a group the syntax is the same
upvoted 1 times
...
tomzus
5 years, 7 months ago
Agree with Guest the create login with password look wrong it would be create login [loginName] from windows. Also agree with msamm the stored procedure and table are two different object that you need to grant access to individually
upvoted 1 times
...
Guest
5 years, 7 months ago
Think the first part should be CREATE LOGIN ... FROM WINDOWS As with password creates an SQL login and the question specifies for a windows authenticated login However it would be for a group instead of for a specific users as requested
upvoted 5 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