DBA > Job Interview Questions > Microsoft SQL Server FAQs

No column permissions are assigned. What should

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

No column permissions are assigned. What should you do?

You are developing security policy for your SQL Servers and have all of the data entry clerks needing access to a series of tables. You create a Data Entry role and assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are assigned. What should you do?


Answer
Create an new role and DENY permission to the Vacation table for this role. Add Bob to this role.
Explanation
To effectively handle security, you want to minimize the administrative burden. Bob is a member of the Data Entry role, and because of cross training, you do not want to remove him from this role, but you do need to DENY permission to the Vacation table. The best way to do this is with another role specifically to DENY this permission.

(Continued on next question...)

Other Job Interview Questions