If you‘re getting ready to take an MCITP exam on SQL Server 2005 (such as 70-441, 70-442, 70-444), you’ll do better if you have a solid understanding of stored procedure permissions.
For example, what’s the answer to this question?
Q. You’ve just developed a stored procedure that can be used to update data in the Widgets table or add new Widgets to the Widgets table if they don’t exist. What permissions are required for someone to use the stored procedure? (Choose all that apply).
A. SELECT permission on the Widgets table.
B. UPDATE permission on the Widgets table.
C. INSERT permission on the Widgets table.
D. EXEC permission on the stored procedure.
Answer: D only.
One of the great benefits of a stored procedure is that permissions are chained to the underlying table. In other words, if someone has EXEC permissions on the stored procedure, they don’t need any permission on the underlying table.
If the stored procedure wasn’t doing the work, users would need INSERT permission on the Widgets table to add a new widget, and UPDATE permission on the Widgets table to modify data on an existing widget. However, if the user is using the stored procedure, only EXEC permission is needed.
This can be one way to help ensure data integrity on the base tables. You don’t grant any permission to the base tables, but instead only grant permissions to the stored procedure. Now you’re assured that the only way the tables are modified is through the stored procedure.
If the stored procedure is solid, your data has a much better chance of staying solid too.
You’ll find exercises in both of the following books demonstrating concepts related to stored procedures permissions in hands-on exercises:
MCITP: Database Administration (70-431, 70-433, and 70-444)
MCITP: Database Developer (70-431, 70-441, 70-442)
Darril
mcitpsuccess.com
No comments:
Post a Comment