You administer a Microsoft SQL Server 2012 database. The database has a table named Customers
owned by UserA and another table named Orders owned by UserB. You also have a stored
procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from
both tables.
You create a new user named UserC.
You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need
to assign only the minimum required permissions to UserC.
Which permission or permissions should you assign to UserC? Choose all that apply.
A.
The Select permission on Customers
B.
The Execute permission on GetCustomerOrderInfo
C.
The Take Ownership permission on Customers
D.
The Control permission on GetCustomerOrderInfo
E.
The Take Ownership permission on Orders
F.
The Select permission on Orders
Explanation:
http://msdn.microsoft.com/en-us/library/ms188676.aspx
http://stackoverflow.com/questions/2212044/sql-server-how-to-permission-schemas
http://sqlservercentral.com/blogs/steve_jones/2012/03/14/ownership-chains-in-sql-server
B is enough to answer this question
Agreed
http://stackoverflow.com/questions/3815411/stored-procedure-and-permissions-is-execute-enough
I agree, B is enough. The Select permissions don’t need specifying, the proc assumes the user is permitted as they have execute rights to the proc.
The only time the select permissions would need explicitly granting is where there’s dynamic SQL in the proc.
Its A and B because of ownership chaining.
if there is just B, when procedure execute code that need to view data from table which is owned by user A it need to check permissions on that table, because SP is owned by another user, and if it see that user C does not have permission on that table, data will not be retrieve.
If there is dynamic SQL in stored procedure, then it should be granted select on all involved tables for user that execute procedure, and execute procedure, because with dynamic SQL there is no ownership chaining.
Dimitrije is correct, A and B.
A and B is not correct. If SELECT is granted on all dependent objects, in this case a single table, then we are granting far more objects rights as are necessary.
When we grant EXECUTE we are implictly granting access to the underlying data as well.
Only B.
Is select and Execute permissions are the same? if ‘yes’ the answer is only ‘B’, if ‘no’ the answer should include both ‘A’, ‘B’, & ‘F’. Proc execution permission does not always guarantee for selection permission.To me the question is some how vague
b is enough
Has anyone claiming that B is enough even tried the scenario?
Owner of the procedure needs to have access to both tables before user who has execute can successfully execute the procedure. Nowhere in the text is mentioned what rights B has on table A so it’s to presume he has no rights.
B needs at least SELECT on Customers for EXECUTE to be sufficient. Since he doesn’t have SELECT, A) and B) is correct answer.
Just tested this. Even with DENY Select permissions, the stored proc executes when execute permissions are GRANTed. This is the whole point, right? We use this all the time to abstract access.
So, my thinking is this should be B only
Unless I’m doing something wrong. Found a stackoverflow that explains this better. My head hurts… https://stackoverflow.com/questions/19517251/sql-server-user-permissions-on-stored-procedure-and-underlying-tables
Why is it not nescessary to also give select permissions on orders (I don’t think only B will be enough)? Shouldn’t the right answer be A+B+F?
A+B.
I test only B and got an error ‘The SELECT permission was denied on the object ‘customers’, database ‘Test’, schema ‘UaserA”. When I add A, it run successful.