You administer a Microsoft SQL Server 2012 database named Contoso that contains a
single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permission for all stored procedures in Contoso to
BillingUsers.
Which Transact-SQL statement should you use?
A.
GREATE ROLE proc_caller
GRANT EXECUTE ON Schema : : dbo TO proc_caller
ALTER ROLE proc_caller ADD MEMBER BillingUsers
B.
GRANT EXECUTE ON INFORMATION_SCHEMA.ROUTINES TO BillingUsers
C.
EXEC sp_addrolemember ‘executor’ , ‘BillingUsers’
D.
GREATE ROLE proc_caller
GRANT EXECUTE ON ALL PROCEDURES TO proc_caller
ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
Answer D is wrong. Grant execute on all procedures to proc_caller is wrong syntax. Should be A.
1
0
All answers are questionable since question says: ‘… that contains a single user-defined database ROLE named BillingUsers’. It is not a member. – ?
1
0
https://msdn.microsoft.com/en-us/library/ms189775.aspx
0
0
The Answer is A
I tried D but it did not work
0
0
alter role for flexible database role used to rename the name the role not to add members on the role. In order to add member in a flexible database role one should use sp_addrolemember procedure.
All the choice are very much role. the correct answer is
GRANT EXECUTE ON Schema::dbo TO BillingUsers
0
0
Alter Role statement can be used to add members. See https://msdn.microsoft.com/en-us/library/ms189775%28v=sql.110%29.aspx
So C is not correct.
The only answer that would work in this situation is A with corrected syntax.
Here is a test script if you want to try. Create an empty TestDB first.
USE [TestDB]
Go
CREATE ROLE BillingUsers
Go
CREATE PROCEDURE up_testSp AS SELECT ‘1’
Go
CREATE USER [B_User1] WITHOUT LOGIN
GO
— Add this user to existing role for testing purpose
ALTER ROLE [BillingUsers] ADD MEMBER [B_User1]
GO
— Execute the procedure using the test user
EXECUTE as user=’B_User1′ Execute up_testSp
REVERT
–You get following error:
–Msg 229, Level 14, State 5, Procedure up_testSp, Line 1
–The EXECUTE permission was denied on the object ‘up_testSp’, database ‘TestDB’, schema ‘dbo’.
— Now run the Option A with corrected syntax, of course there is no
–such thing as GREATE with “G”
CREATE ROLE proc_caller
GRANT EXECUTE ON Schema::dbo TO proc_caller
ALTER ROLE proc_caller ADD MEMBER BillingUsers
— Now run the same procedure again to test it works.
EXECUTE as user=’B_User1′ Execute up_testSp
REVERT
0
0
BillingUsers is a role, not a member. The correct answer is option C:
EXEC sp_addrolemember ‘executor’ , ‘BillingUsers’
0
0
The question didn’t mention that a new role ‘executor’ has been created which has been given Execute permissions. So C is not correct.
The nearest correct answer is A as per my previous post.
0
0
Please read about sp_addrolememberand you will know why this is the correct answer.
0
0
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.
0
0
I agree with you C is the correct answer.
0
0
To opt C as correct, executor role should already been created. I don’t find this in the question.
0
0
Invalidating A as the answer by saying that “BillingUsers is a role, not a member” would demonstrate a misunderstanding of roles.
BillingUsers has an SID, and appears in sys.database_principals: therefore, it can be a member of a role, which is why you can have role hierarchies (roles within roles).
Consider the following, where one would create a simple role hierarchy:
Use master
GO
Create ROLE A authorization dbo
Create ROLE B authorization dbo
Alter Role A add member B
Select * from sys.database_principals
Select * from sys.database_role_members
Drop Role B
Drop Role A
Notice by running this query (assuming you have rights) that
– A is role
– B is a role
– the member_principal_id of B is a member of role_principal_id of A, meaning that B is a member
This being said, answer A is still correct.
0
0
Slazenjer…you do not seem to be that good in user-rights and stuff. Please refrain yourself from commenting. You are wrong in almost everything related to rights.
On topic: Answer A is correct, which can be easily tested…..I suggest you do!
0
0
It’s been three months. Slazenjer is not going to see your post. He could get all the rights thing wrong and still pass. He really is terrible on rights. And he’s been using sql since 6.5!
0
0
How can you guys be so familiar with the same guy, lol.
0
0
I Think they are all wrong. I agree with Dereje.
correct answer is:
GRANT EXECUTE ON Schema::dbo TO BillingUsers
0
0
Why do we need to create a new role
0
0