Top 20 SQL Server Security Interview Questions

1. If password policy is enforced; can you tell me the new password rules and policies to make sure the password is strong?

2. Have you ever implemented Application Roles in your environment?

3. What are the advantages and disadvantages of a contained database feature?

4. What is the new security features added in SQL Server 2012 / 2014 /2016?

5. What are the most common symbols/operators used for SQL Injection?

6. What are the different ways to prevent SQL Injection?

7. I have a premium SQL Server 2012 database where data is highly sensitive. My requirement is to audit all “Delete” operations against the table “CCTran” on database “OrgStore.” Can you describe the steps required to configure SQL Audit to fulfill this requirement.

8. What are the impersonation options available?

9. What is the proxy account in SQL Server?

10. Does TDE (Transparent Data Encryption) supports in all SQL Server High Availability and Disaster Recovery features?

11. Does TDE prevent the security administrator or database administrator from seeing the data in the database?

12. You are assigned as a database architect for one of the premium project. Client asked you the question “What are the areas where we should configure proper security principles and need to follow best practices?” How do you answer the question?

13. Since we are looking for a SQL Server SME, we would expect you to define and implement security best practices in our enterprise database environment. From your experience can you list out few points to enforce security for SQL Server?

14. How to resolve the orphan user problem?

15. Can we be able to find out who changed the password for a SQL Login? If yes explain.

16. What is the quickest way to list out all database objects and their permissions to user details?

17. We have a role R1 created. This role is granted to INSERT and UPDATE on all tables. I have mapped 20 users to this role R1. Now these 20 users have got the INSERT and UPDATE permission as the role is granted. But now I wanted to DENY INSERT permission for one of those 20 users. Is it possible without detaching that user from the role R1?

18. Our client required to give TRUNCATE permission for one of the programmer and below is the requirement:

• The user “PRG01” should be able to truncate 2 tables
• Except those 2 tables he/she shouldn’t be able to TRUNCATE any other tables
• We shouldn’t give “ALTER TABLE” permission.
• Also for your information these 2 tables are not having/referencing foreign keys

Now tell me what is your solution?

19. We have created a SQL Agent job to execute a SSIS package and the job started failing with the message “Non-SYSADMINS have been denied permission to run DTS Execution job steps without a proxy account.” Any idea how to resolve this issue?

20. What are some of the pros and cons of not dropping the SQL Server BUILTIN\ Administrators Group?