22 + Interview Questions in Security in SQL Server Page 1 InterviewSolution

1.	Which of the following will not return user name of the current session?(a) CUURENT_USER()(b) USER_NAME()(c) CUSER_NAME()(d) None of the mentionedI got this question in semester exam.I'd like to ask this question from Row Level Security topic in chapter Security of SQL Server
Answer» Right option is (d) NONE of the mentioned Easy EXPLANATION: All of the specified FUNCTIONS will RETURN user name.

Discussion

2.	Return type of CertName field in vwVisibleLabels is __________(a) XML(b) INT(c) NVARCHAR(256)(d) VARCHAR(256)This question was addressed to me in an international level competition.I need to ask this question from Row Level Security in portion Security of SQL Server
Answer» Correct CHOICE is (c) NVARCHAR(256) The EXPLANATION: CertName field DESCRIBES the security CERTIFICATE name, to implement Cell Level Security.

Discussion

3.	The major components of the Label Security Toolkit are _____________(a) Documentation(b) The Label Policy Designer application(c) Examples showing the implementation of row- and cell-level security in different scenarios(d) All of the mentionedI got this question by my college professor while I was bunking the class.I need to ask this question from Row Level Security in chapter Security of SQL Server
Answer» The correct OPTION is (d) All of the mentioned Explanation: The Label Security Toolkit provides tools and techniques for using Microsoft® SQL Server (versions 2005 through 2012) to IMPLEMENT row-level security (RLS) and cell-level security (CLS) based on security LABELS.

Discussion

4.	What will the fn_Dominates return if A dominates B where A and B both are security label?(a) 0(b) 1(c) NULL(d) None of the mentionedI got this question by my school principal while I was bunking the class.The query is from Row Level Security topic in section Security of SQL Server
Answer» The CORRECT answer is (b) 1 To EXPLAIN I would say: fn_Dominates (FUNCTION) compares TWO labels and determines whether label A DOMINATES label B.

Discussion

5.	Point out the wrong statement.(a) usp_EnableCellVisibility opens all the symmetric keys that are mapped to security labels(b) usp_DisableCellVisibility opens all the symmetric keys that were previously opened(c) On SQL Server 2012 you can use the Contained Database feature to create a user without a login(d) No arguments are available for usp_EnableCellVisibilityThis question was posed to me by my college director while I was bunking the class.Asked question is from Row Level Security topic in division Security of SQL Server
Answer» The correct answer is (b) usp_DisableCellVisibility opens all the symmetric keys that were previously OPENED Best EXPLANATION: usp_DisableCellVisibility (STORED Procedure) closes all the symmetric keys that were previously opened by CALLING usp_EnableCellVisibility.

Discussion

6.	Which of the following retrieves a SecurityLabel instance describing the subject label of the current database user?(a) fn_Dominates(b) usp_GetUserLabel(c) usp_GetCurrentUserLabel(d) usp_GetSecLabelDetailsThe question was asked during an online exam.Origin of the question is Row Level Security in section Security of SQL Server
Answer» The CORRECT CHOICE is (c) usp_GetCurrentUserLabel The explanation: usp_GetSecLabelDetails generates the security label identifier and ENCRYPTION objects for the SPECIFIED label.

Discussion

7.	View that contains the list of all the security labels present in the database are __________(a) vwVisibleLabels(b) VisibleLabels(c) vwVisibleLabel(d) All of the mentionedThis question was posed to me in homework.Asked question is from Row Level Security topic in section Security of SQL Server
Answer» The CORRECT choice is (a) vwVisibleLabels Easiest EXPLANATION: The purpose of vwVisibleLabels view is to enforce the row security by JOINING it with the base table.

Discussion

8.	Point out the correct statement.(a) Implementing row level security based on security labels is possible in SQL Server 2008(b) A security label is a marking that describes the securable content of an item(c) Row-level permissions are used for applications that store information in a single table(d) None of the mentionedThis question was addressed to me by my school principal while I was bunking the class.This question is from Row Level Security topic in portion Security of SQL Server
Answer» CORRECT answer is (c) Row-level permissions are used for applications that store information in a SINGLE table To explain I would SAY: SQL SERVER does not have support for implementing row-level security. You must create additional columns in your tables that define row filtering mechanisms.

Discussion

9.	Which of the following condition in the where clause will return the login identification name of the user?(a) UserName = SUSER_NAME()(b) UserName = SUSER_SNAME()(c) UserName = CURRENT_USER()(d) UserName = USER()I had been asked this question in exam.The origin of the question is Row Level Security in chapter Security of SQL Server
Answer» RIGHT option is (b) UserName = SUSER_SNAME() To ELABORATE: USER_NAME or CURRENT_USER RETURN the database user name.

Discussion

10.	Any SQL Server authentication login can see ________ login name.(a) Own and sa(b) sa(c) Own(d) None of the mentionedI have been asked this question in an online interview.Asked question is from Securables topic in section Security of SQL Server
Answer» Right OPTION is (a) Own and sa Explanation: To SEE other LOGINS, requires ALTER ANY LOGIN, or permission on the login.

Discussion

11.	Which of the following returns a row for each security principal in a SQL Server database?(a) sys.database_role_members(b) sys.sql_logins(c) sys.database_principals(d) none of the mentionedI had been asked this question by my school principal while I was bunking the class.The above asked question is from Securables topic in section Security of SQL Server
Answer» RIGHT OPTION is (c) sys.database_principals Explanation: The permissions of fixed DATABASE ROLES do not appear in sys.database_permissions.

Discussion

12.	The schema securable scope contains the following securable is __________(a) Contract(b) Asymmetric key(c) Schema(d) ObjectThe question was asked in my homework.My doubt stems from Securables in section Security of SQL Server
Answer» The correct OPTION is (d) Object To explain I WOULD SAY: The object class has the following MEMBERS: Aggregate, Function and Procedure.

Discussion

13.	Point out the wrong statement.(a) The entity that receives permission to a securable is called a principal(b) The SCHEMA securable scope contains the USER securables(c) A table is a securable(d) None of the mentionedThis question was posed to me during an interview for a job.I'd like to ask this question from Securables in portion Security of SQL Server
Answer» Correct choice is (b) The SCHEMA securable SCOPE CONTAINS the USER securables For EXPLANATION: The database securable scope contains the user securables.

Discussion

14.	What is the syntax for fn_my_permissions function in SQL Server 2014?(a) fn_my_permissions ( securable , securable_class )(b) fn_my_permissions ( securable , ‘securable_class’ )(c) fn_my_permissions ( ‘securable’ , securable_class)(d) fn_my_permissions ( ‘securable’ , ‘securable_class’ )The question was posed to me in a job interview.This question is from Securables topic in portion Security of SQL Server
Answer» The correct option is (b) fn_my_permissions ( SECURABLE , ‘securable_class’ ) EXPLANATION: Securable is the name of the securable. If the securable is the server or a DATABASE, this value should be set to NULL. securable is a scalar expression of TYPE sysname. securable can be a multipart name.

Discussion

15.	The Windows password policies enforced for SQL Server logins depends upon _____________________ API.(a) NetValidate(b) NetValidatePasswordPolicy(c) NetValidatePolicy(d) ValidatePasswordPolicyI got this question in homework.This interesting question is from Authentication topic in division Security of SQL Server
Answer» RIGHT answer is (b) NETVALIDATEPASSWORDPOLICY To elaborate: NetValidatePasswordPolicy API is only available in WINDOWS SERVER 2003 and LATER versions.

Discussion

16.	By using Windows Authentication, Windows groups can be created at the _________ level.(a) Login(b) Domain(c) User(d) None of the mentionedThis question was posed to me in final exam.Asked question is from Authentication topic in portion Security of SQL Server
Answer» Correct answer is (B) Domain Best EXPLANATION: Managing access from the domain LEVEL can simplify account ADMINISTRATION.

Discussion

17.	________ optional password policies are available for SQL Server logins.(a) 2(b) 3(c) 5(d) All of the mentionedThe question was asked in examination.This intriguing question comes from Authentication in portion Security of SQL Server
Answer» Right answer is (B) 3 The best explanation: When using SQL Server AUTHENTICATION, logins are CREATED in SQL Server that are not BASED on Windows user accounts.

Discussion

18.	Which of the following is the biggest disadvantage of SQL Server Authentication mode?(a) SQL Server Authentication cannot use Kerberos security protocol(b) Allows SQL Server to support Web-based applications where users create their own identities(c) Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication(d) None of the mentionedI got this question in exam.The query is from Authentication in portion Security of SQL Server
Answer» The correct ANSWER is (a) SQL Server AUTHENTICATION cannot use Kerberos security PROTOCOL The explanation: WINDOWS Authentication uses Kerberos security protocol.

Discussion

19.	Which of the following authentication modes is more secure?(a) SQL Server Authentication(b) Windows Authentication(c) Mixed Mode(d) All of the mentionedThis question was posed to me in exam.I'm obligated to ask this question of Authentication in section Security of SQL Server
Answer» Correct answer is (b) Windows Authentication Explanation: Windows Authentication USES Kerberos security protocol, PROVIDES password policy ENFORCEMENT with REGARD to complexity VALIDATION for strong passwords.

Discussion

20.	Windows Authentication Mode is not available when an instance of SQL Server is running on ____________(a) Windows 98(b) Windows 7(c) Windows 95(d) All of the mentionedI have been asked this question during an interview for a job.The query is from Authentication in section Security of SQL Server
Answer» Correct option is (a) WINDOWS 98 To EXPLAIN I WOULD SAY: Windows AUTHENTICATION Mode is also unavailable on Microsoft Windows Millennium Edition.

Discussion

21.	Point out the wrong statement.(a) Windows authentication mode dramatically reduces the chances of error or oversight(b) Account administration details in windows mode is stored in passive directory(c) The sa account is a well-known SQL Server account and it is often targeted by malicious users(d) All of the mentionedThis question was addressed to me during an online interview.This question is from Authentication topic in chapter Security of SQL Server
Answer» The correct option is (b) ACCOUNT administration details in windows mode is STORED in PASSIVE DIRECTORY Easiest EXPLANATION: Account administration details in windows authentication mode are stored in single place:Active directory.

Discussion

22.	Which of the following feature prefers SQL Server authentication mode over windows authentication?(a) Backward compatibility(b) Forward compatibility(c) Security(d) All of the mentionedThis question was posed to me in unit test.This question is from Authentication in section Security of SQL Server
Answer» The correct choice is (a) Backward compatibility The BEST explanation: SQL Server AUTHENTICATION is provided for backward compatibility. For example, if you create a single Windows 2000 GROUP and add all NECESSARY users to that group you will need to grant the Windows 2000 group login rights to SQL Server and access to any necessary DATABASES.

Discussion

Explore topic-wise InterviewSolutions in .

Which of the following will not return user name of the current session?(a) CUURENT_USER()(b) USER_NAME()(c) CUSER_NAME()(d) None of the mentionedI got this question in semester exam.I'd like to ask this question from Row Level Security topic in chapter Security of SQL Server

Return type of CertName field in vwVisibleLabels is __________(a) XML(b) INT(c) NVARCHAR(256)(d) VARCHAR(256)This question was addressed to me in an international level competition.I need to ask this question from Row Level Security in portion Security of SQL Server

What will the fn_Dominates return if A dominates B where A and B both are security label?(a) 0(b) 1(c) NULL(d) None of the mentionedI got this question by my school principal while I was bunking the class.The query is from Row Level Security topic in section Security of SQL Server

View that contains the list of all the security labels present in the database are __________(a) vwVisibleLabels(b) VisibleLabels(c) vwVisibleLabel(d) All of the mentionedThis question was posed to me in homework.Asked question is from Row Level Security topic in section Security of SQL Server

Any SQL Server authentication login can see ________ login name.(a) Own and sa(b) sa(c) Own(d) None of the mentionedI have been asked this question in an online interview.Asked question is from Securables topic in section Security of SQL Server

The schema securable scope contains the following securable is __________(a) Contract(b) Asymmetric key(c) Schema(d) ObjectThe question was asked in my homework.My doubt stems from Securables in section Security of SQL Server

By using Windows Authentication, Windows groups can be created at the _________ level.(a) Login(b) Domain(c) User(d) None of the mentionedThis question was posed to me in final exam.Asked question is from Authentication topic in portion Security of SQL Server

________ optional password policies are available for SQL Server logins.(a) 2(b) 3(c) 5(d) All of the mentionedThe question was asked in examination.This intriguing question comes from Authentication in portion Security of SQL Server

Which of the following authentication modes is more secure?(a) SQL Server Authentication(b) Windows Authentication(c) Mixed Mode(d) All of the mentionedThis question was posed to me in exam.I'm obligated to ask this question of Authentication in section Security of SQL Server

Windows Authentication Mode is not available when an instance of SQL Server is running on ____________(a) Windows 98(b) Windows 7(c) Windows 95(d) All of the mentionedI have been asked this question during an interview for a job.The query is from Authentication in section Security of SQL Server

Which of the following feature prefers SQL Server authentication mode over windows authentication?(a) Backward compatibility(b) Forward compatibility(c) Security(d) All of the mentionedThis question was posed to me in unit test.This question is from Authentication in section Security of SQL Server