You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01.
Certkiller -DB01 hosts a database named CK_Products that contains 60 lookup tables. The lookup tables should remain static at all times and users should not be able to alter the static data. You discover that some of the data in the lookup tables have been altered. You need to ensure that users cannot change any of the data in the lookup tables.
What should you do?
A.
Create a new filegroup and move the lookup tables to the new filegroup.
Enable the Read-Only option on the filegroup.
B.
Create a view of the lookup tables.
Allow users to access the lookup tables through the view only.
C.
Create stored procedures for modifying data in the lookup tables.
Allow users to modify data through the stored procedures only.
D.
Create a new database role and add all users to the new role.
Grant SELECT permissions to the new role.
Explanation:
You can prevent users form modifying data in the lookup tables by moving the tables to a separate filegroup and making the file group read-only.
Incorrect Answers:
B: Views are used to limit the part of the database that the users can see. It does not prevent them from modifying the data in the underlying table.
C: Users should not be able to modify the data in the lookup tables. Allowing them to modify data through the stored procedures only would not meet this requirement.
D: This option will require considerable administrative effort as there are 60 look up tables. You will need to grant SELECT permissions for each of these tables. Using a read-only filegroup would require less effort.
Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: filegroups [SQL Server], read-only
Microsoft SQL Server 2005 Books Online (2007), Index: views [SQL Server] Microsoft SQL Server 2005 Books Online (2007), Index: database roles [SQL Server] Microsoft SQL Server 2005 Books Online (2007), Index: stored procedures [SQL Server]