locked
Alter View to prevent other users from copying the view RRS feed

  • Question

  • This a sample question from MCTS 70-433 exam. I would like to show you what the question was and what the choices for the answers were and see if you agree with me that this is a wierd question... or may be I am not understanding something.

    The question asks,

    You have created a view using the following command:

    create view dbo.Employee
    with encryption
    as select Name from dbo.HumanResources
    where isEmployee = 1
    with check option;

    You must prevent other users from copying the view.
    Which statement should you use?

    The choices are

    (a):
    alter view dbo.Employee
    as select Name from dbo.HumanResources
    where isEmployee = 1
    with check option;

    (b):
    alter view dbo.Employee
    with encryption
    as select Name from dbo.HumanResources
    where isEmployee = 1

    (c):
    alter view dbo.Employee
    with schemabinding
    as select Name from dbo.HumanResources
    where isEmployee = 1

    (d): deny all on dbo.Employee to public

    So there you have it. I'll wait for some of you to pick an answer before I tell you why I think this is a wierd question.

    Friday, July 17, 2009 8:29 PM

Answers

  • Using DENY ALL simply means deny SELECT, INSERT, DELETE, UPDATE, REFERENCES.

    But try creating/altering the view WITH ENCRYPTION and then run:

    sp_helptext 'dbo.Employee';

    You will get "The text for object 'dbo.Employee' is encrypted." which pretty much points the correct answer.
    Plamen Ratchev
    Friday, July 17, 2009 8:57 PM

All replies

  • Using DENY ALL simply means deny SELECT, INSERT, DELETE, UPDATE, REFERENCES.

    But try creating/altering the view WITH ENCRYPTION and then run:

    sp_helptext 'dbo.Employee';

    You will get "The text for object 'dbo.Employee' is encrypted." which pretty much points the correct answer.
    Plamen Ratchev
    Friday, July 17, 2009 8:57 PM
  • Using DENY ALL simply means deny SELECT, INSERT, DELETE, UPDATE, REFERENCES.

    But try creating/altering the view WITH ENCRYPTION and then run:

    sp_helptext 'dbo.Employee';

    You will get "The text for object 'dbo.Employee' is encrypted." which pretty much points the correct answer.
    Plamen Ratchev


    That's correct. So (b) is the answer.

    But don't you think this is a wierd question since the original create view statement already has the WITH ENCRYPTION option?
    I guess this is the best answer given the choices, but it was confusing when I was taking the practice exam because I was thinking to myself, the view is already encrypted so what else do I need to do? But if the answer is (b), then I am not doing anything to alter the encryption because it is already encrypted. The only thing (b) does is to get rid of the WITH CHECK option which has nothing to do with the question. So I thought this was a wierd question.

    Friday, July 17, 2009 9:06 PM
  • Many questions are not very straight forward... Perhaps it is the point of making a confusing question and finding the most accurate answer. :)
    Plamen Ratchev
    Friday, July 17, 2009 10:19 PM
  • I agree with Plamen and found that this question was to create confusing question.

    I am preparing for the certification as well. Just wanted to find if this is resource is enough to take the exam ? Is anybody using any other resource to give the exam.

     

     

    Saturday, October 22, 2011 4:08 PM
  • But this is a job for DCL and not DDL. The data element names are a mess and they write with bit flags, too. Bad question. 
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Sunday, October 23, 2011 3:44 AM