MS SQL Server - 45 - Database System Security MCQ

45 – Database System Security MCQ

Best of luck!

1 / 25

1. What is the primary purpose of setting up database system security in SQL Server?

To protect data and ensure unauthorized access is prevented

To define database schemas

To optimize query performance

To manage database backups

2 / 25

2. Which SQL Server feature is used to control user access and permissions at the database level?

Clustering

Database Security

Replication

Database Mirroring

3 / 25

3. What is the primary function of SQL Server roles in database security?

To define database schemas

To store data encryption keys

To manage user authentication

To group users with similar permissions

4 / 25

4. In SQL Server, what is the minimum role required to create and modify other user roles?

db_owner

db_datawriter

db_datareader

db_securityadmin

5 / 25

5. Which system role in SQL Server is used to manage user logins and passwords?

db_datareader

db_owner

sysadmin

securityadmin

6 / 25

6. In SQL Server, what is the purpose of the sysadmin fixed server role?

To control user access to a specific database

To manage database schemas

To create user-defined functions

To administer server-wide settings and configurations

7 / 25

7. Which SQL Server role allows a user to read data from all user tables within a database?

db_datawriter

db_datareader

db_securityadmin

db_owner

8 / 25

8. In SQL Server, what does the db_datawriter role allow a user to do?

Read data from all user tables

Create and modify tables

Execute stored procedures

View database schemas

9 / 25

9. Which SQL Server role allows a user to modify data in all user tables within a database?

db_datareader

db_securityadmin

db_datawriter

db_owner

10 / 25

10. In SQL Server, what is the primary function of the db_owner role?

To read data from all user tables

To manage user logins

To execute stored procedures

To create and manage database schemas

11 / 25

11. Which SQL Server role has the highest level of privilege and control over the database?

public

db_datareader

db_owner

db_datawriter

12 / 25

12. What is the purpose of the public role in SQL Server?

To manage user authentication

To group users with similar permissions

To administer server-wide settings

To create and modify user roles

13 / 25

13. In SQL Server, what is the primary purpose of database-level permissions?

To manage server configurations

To control user access to the server

To specify what actions users can perform within a database

To create user logins

14 / 25

14. Which SQL Server statement is used to grant a user database-level permissions?

GRANT DATABASE

GRANT PERMISSION

GRANT SERVER

GRANT LOGIN

15 / 25

15. What is the primary function of database schemas in SQL Server?

To control user access to the server

To organize database objects into logical containers

To manage database backups

To store user data

16 / 25

16. Which SQL Server statement is used to create a new schema in a database?

CREATE DATABASE SCHEMA

CREATE USER SCHEMA

CREATE SCHEMA

CREATE TABLE SCHEMA

17 / 25

17. What is the purpose of the GRANT statement in SQL Server?

To modify database schemas

To create new user logins

To execute stored procedures

To assign permissions to users and roles

18 / 25

18. In SQL Server, what is the minimum required permission to create a new database user?

ALTER ANY USER

db_datareader

db_datawriter

db_owner

19 / 25

19. Which SQL Server statement is used to assign a role to a user?

ASSIGN ROLE

GRANT ROLE

ALTER ROLE

ADD ROLE

20 / 25

20. In SQL Server, what is the purpose of the DENY statement?

To assign roles to users

To create new schemas

To explicitly deny specific permissions to users and roles

To grant permissions to users and roles

21 / 25

21. What is the primary purpose of the REVOKE statement in SQL Server?

To create new schemas

To assign roles to users

To remove previously granted permissions from users and roles

To grant permissions to users and roles

22 / 25

22. Which SQL Server statement is used to remove a role from a user?

ASSIGN ROLE

DROP ROLE

REVOKE ROLE

ALTER ROLE

23 / 25

23. In SQL Server, what is the primary purpose of the EXECUTE AS statement?

To create new user logins

To assign roles to users

To execute a stored procedure or function with a specified user context

To grant server-level permissions

24 / 25

24. What is the primary function of server-level roles in SQL Server?

To group users with similar permissions within a database

To manage user logins and server-wide settings

To create and manage database schemas

To grant or deny permissions at the server level

25 / 25

25. Which SQL Server role is automatically assigned to every user in a database and cannot be removed?

public

db_datareader

db_owner

db_datawriter

Your score is

MS SQL Server – 45 – Database System Security MCQ