You administer a Microsoft SQL Server 2012 database named Contoso that contains a single userdefined 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.
CREATE 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.
CREATE ROLE proc_caller
GRANT EXECUTE ON ALL PROCEDURES TO proc_caller
ALTER MEMBER BillingUsers ADD TO ROLE proc_caller
I would choose answer A since is the correct syntax.
— SQL Server Syntax
ALTER ROLE role_name
{
[ ADD MEMBER database_principal ]
| [ DROP MEMBER database_principal ]
| WITH NAME = new_name
}
[;]
B is not granting execute on all procedures.
C there isn´t any predefined role called executor.
D Bad syntax. Also Alter member does not seem to exist.
Yeah good shout Javier I am with you the correct syntax for this is Answer A
I don’t think the syntax is the trick. Obviously its wrong. It’s whether the execute should be granted on schema::billing or the entire database, the right syntax for which is:
Grant execute to [user] with out the “on” qualifier. There it no “to all”. Anyway, the questions says “All objects” are in the schema and execute must be granted to “all stored procedures”. If there is never a chance that a stored procedure will be made outside the schema billing or the questions meant that execute must be granted to all stored procedures at the time the question is asked, A is best. If we must cover the possibility that future stored procedures could be made or moved outside of the schema, a D with the proper syntax is the right answer. Obviously, if this was an actual question on the Test, A is best because D is syntactically flawed.
A
A -> https://www.google.bg/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&ved=0ahUKEwiMqOXXs9XLAhWGCJoKHTd0AtEQFggvMAM&url=http%3A%2F%2Fstackoverflow.com%2Fquestions%2F9321334%2Fgrant-execute-to-all-stored-procedures&usg=AFQjCNElvUA5_thDZdOfOSNsU-yleQAszQ&sig2=GNlSHI3rxOtsGFyHhYiZ5A
A