You are the database administrator of your company. The network contains an instance of SQL Server 2008. Thenetwork also contains a Web server that hosts a Web application to provide product information to customers.The product information is stored in a database named Products on the SQL server. You want to create a stored procedure for use by the Web application that will return information about productsfrom the Products database that matches the search criteria specified by the customer.
Which two permissions will you require to perform this task? (Choose two. Each correct answer represents a partof the solution.)
A.
the CREATE PROCEDURE permission in the Products database
B.
the CREATE PROCEDURE permission in the master database
C.
the ALTER permission on the schema in which the stored procedure is being created
D.
the UPDATE permission on the schema in which the stored procedure is being created
Explanation:
You will require the CREATE PROCEDURE permission in the Products database and the ALTER permission onthe schema in which the stored procedure is being created. A stored procedure is a collection of Transact-SQLstatements that are saved in the database. A stored procedure can accept parameters supplied by a user andreturn output parameters to the user. To create stored procedures in a database, you must have the CREATEPROCEDURE permission in the database and ALTER permission on the schema in which the stored procedureis being created. The three types of stored procedures are user-defined stored procedures, extended stored procedures, andsystem stored procedures. Transact-SQL and Common Language Runtime (CLR) stored procedures are referredto as user-defined stored procedures. Extended stored procedures allow you to create your own external routinesby using an alternate programming language. Extended stored procedures are dynamic link libraries (DLLs) thatcan be dynamically loaded and run by an instance of SQL Server. System stored procedures are built into theinstance of SQL Server and allow you to perform several administrative tasks.
Note: PowerShell providers allow you to access the hierarchy of SQL Server objects by using a drive and pathstructure similar to Windows file system. You will not require the
CREATE PROCEDURE permission in the master database. A stored procedure can becreated for permanent or temporary use. A stored procedure can only be created in the current database exceptfor the temporary procedures because temporary stored procedures are always created in the tempdb database.In this scenario, the product information is stored in the Products database. Therefore, you will require the CREATE PROCEDURE permission in the Products database. You will not require the UPDATE permission on the schema in which the stored procedure is being created. Tocreate a stored procedure, you must have the ALTER permission on the schema in which the stored procedure isbeing created. The UPDATE
permission allows users to update existing data in a table or a view.Objective:
Managing SQL Server SecuritySub-Objective:
Manage schema permissions and object permissions.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData > Stored Procedures > Implementing Stored Procedures > Creating Stored Procedures (Database Engine) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE PROCEDURE (Transact-SQL)