This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Securables”.
1. What is the syntax for HAS_PERMS_BY_NAME in SQL Server 2014?
a)
HAS_PERMS_NAME ( securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )
b)
HAS_PERMS_BY_NAME ( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )
c)
HAS_PERMS_BY_NAME ( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )
d)
HAS_PERMS( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] )
Explanation: HAS_PERMS_BY_NAME evaluates the effective permission of the current user on a securable.
2. Point out the correct statement.
a) In versions of SQL Server through SQL Server 2014, sub-securables cannot use brackets in the form ‘[sub name]’. Use ‘sub name’ instead
b) HAS_PERMS_BY_NAME returns NULL when the query fails
c) In ADO.net, the sub-securable_class argument is valid only if the securable_class argument is set to OBJECT
d) None of the mentioned
View Answer
Explanation: Use ‘sub name’ instead of ‘[sub name]’.
3. _________ returns a list of the permissions effectively granted to the principal on a securable.
a) HAS_PERMS_BY_NAME
b) sys.fn_my_permissions
c) sys.fn_permissions
d) All of the mentioned
View Answer
Explanation: Related function is HAS_PERMS_BY_NAME.
4. 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’ )
View Answer
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.
5. 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 mentioned
View Answer
Explanation: The database securable scope contains the user securables.
6. The schema securable scope contains the following securable is __________
a) Contract
b) Asymmetric key
c) Schema
d) Object
View Answer
Explanation: The object class has the following members: Aggregate, Function and Procedure.
7. 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 mentioned
View Answer
Explanation: The permissions of fixed database roles do not appear in sys.database_permissions.
8. Any SQL Server authentication login can see ________ login name.
a) Own and sa
b) sa
c) Own
d) None of the mentioned
View Answer
Explanation: To see other logins, requires ALTER ANY LOGIN, or permission on the login.
9. To add or remove server role membership use ____________
a) ALTER ROLE
b) ALTER ANY LOGIN
c) ALTER SERVER ROLE
d) None of the mentioned
View Answer
Explanation: ALTER SERVER ROLE changes the membership of a server role or changes name of a user-defined server role. Fixed server roles cannot be renamed.
10. What is the role of WITH NAME in the following code?
ALTER SERVER ROLE server_role_name { [ ADD MEMBER server_principal ] | [ DROP MEMBER server_principal ] | [ WITH NAME = new_server_role_name ] } [ ; ]
a) Removes the specified server principal from the server role
b) Adds the specified server principal to the server role
c) Specifies the new name of the user-defined server role
d) None of the mentioned
View Answer
Explanation: WITH NAME =new_server_role_name. This name cannot already exist in the server.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]- Check Information Technology Books
- Check SQL Server Books
- Apply for Programming Internship
- Practice Programming MCQs