SQL Server Questions and Answers – Securables

«
»

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)

advertisement
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)

advertisement
HAS_PERMS( securable , securable_class , permission  
    [ , sub-securable ] [ , sub-securable_class ] )
View Answer
Answer: c
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

Answer: a
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

Answer: b
Explanation: Related function is HAS_PERMS_BY_NAME.

advertisement

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

Answer: b
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

Answer: b
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

Answer: d
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

Answer: c
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

Answer: a
Explanation: To see other logins, requires ALTER ANY LOGIN, or permission on the login.

advertisement

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

Answer: c
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

Answer: c
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.

advertisement
advertisement
advertisement
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer & SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage, Advanced C Programming, SAN Storage Technologies, SCSI Internals & Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him @ LinkedIn