A or B

There is a scanario like there are two tables name department and other is
Employee. Each department manager has been added to the managers database
role. You need to allow members of this database role to view all of the
data in the department table. Members of this role should be able to insert
or update only the row that pertains to their department. You grant the
managers database role SELECT permissions on the department table.
What should you do next?
A. Create a trigger on the department table that checks whether the database
login of the user performing the insert or update operation belongs to a
member of that department.
B. Create a view that includes all columns in the department table and the
SQLLogin column from the employees table.include the WITH CHECK OPTION
clause in the view definition.
Thanks
NOOR
Easy choice as I don't like updateable views at all. Seriously though, I'd
suggest:
C. Create an update stored proc that implements the appropriate logic.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Noor" <noor@.ngsol.com> wrote in message
news:%233IJ5ZZkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> There is a scanario like there are two tables name department and other is
> Employee. Each department manager has been added to the managers database
> role. You need to allow members of this database role to view all of the
> data in the department table. Members of this role should be able to
insert
> or update only the row that pertains to their department. You grant the
> managers database role SELECT permissions on the department table.
> What should you do next?
> A. Create a trigger on the department table that checks whether the
database
> login of the user performing the insert or update operation belongs to a
> member of that department.
> B. Create a view that includes all columns in the department table and the
> SQLLogin column from the employees table.include the WITH CHECK OPTION
> clause in the view definition.
> Thanks
> NOOR
>
|||Between the given two options, I would go with the second one, as SQL Server
will do the checking for you, instead of you creating a trigger and writing
code for it.
You might find this article somewhat related:
Implementing Row Level Security in SQL Server
http://vyaskn.tripod.com/row_level_s..._databases.htm
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Noor" <noor@.ngsol.com> wrote in message
news:%233IJ5ZZkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> There is a scanario like there are two tables name department and other is
> Employee. Each department manager has been added to the managers database
> role. You need to allow members of this database role to view all of the
> data in the department table. Members of this role should be able to
insert
> or update only the row that pertains to their department. You grant the
> managers database role SELECT permissions on the department table.
> What should you do next?
> A. Create a trigger on the department table that checks whether the
database
> login of the user performing the insert or update operation belongs to a
> member of that department.
> B. Create a view that includes all columns in the department table and the
> SQLLogin column from the employees table.include the WITH CHECK OPTION
> clause in the view definition.
> Thanks
> NOOR
>