MS SQL Server - 47 - Managing Database Security MCQ

47 – Managing Database Security MCQ

Best of luck!

1 / 25

1. What is the primary purpose of SQL Server Management Studio (SSMS) in managing database security?

To design database schemas

To configure user permissions and roles

To write C# code

To create new databases

2 / 25

2. In SQL Server Management Studio (SSMS), what window can you use to manage database users and their roles?

Template Explorer

Object Explorer

Solution Explorer

Query Window

3 / 25

3. Which SQL Server Management Studio (SSMS) feature allows you to script database objects and permissions for deployment?

Activity Monitor

Query Designer

Object Explorer

Generate Scripts Wizard

4 / 25

4. What is the primary benefit of using the “Securables” tab in SSMS when configuring permissions?

To define login credentials

To generate execution plans

To select the objects on which permissions are set

To specify the server name

5 / 25

5. In Transact-SQL, which statement is used to create a new user in a database?

CREATE DATABASE

CREATE USER

CREATE ROLE

CREATE LOGIN

6 / 25

6. In Transact-SQL, what statement is used to grant SELECT permission on a table to a user?

GRANT SELECT ON Table TO User

GRANT User TO SELECT Table

GRANT SELECT TO User

GRANT USER SELECT ON Table

7 / 25

7. In Transact-SQL, which statement is used to revoke a previously granted permission from a user on a table?

REVOKE USER

REVOKE SELECT FROM User ON Table

REVOKE SELECT ON Table FROM User

REVOKE PERMISSION

8 / 25

8. In SQL Server Management Studio (SSMS), what does the “Effective” tab show when configuring permissions?

It displays the server's hardware specifications.

It displays the server's IP address.

It lists the effective permissions of a user or role on a securable.

It shows the currently executing queries.

9 / 25

9. In Transact-SQL, what statement is used to create a new database role?

CREATE LOGIN

CREATE USER

CREATE ROLE

CREATE DATABASE

10 / 25

10. In Transact-SQL, what statement is used to add a user to a database role?

ALTER ROLE

ALTER USER

ADD USER TO ROLE

ADD ROLE TO USER

11 / 25

11. In Transact-SQL, what is the primary benefit of using the “ALTER ROLE” statement?

To change a user's password

To modify the properties of a role

To create a new database

To add a new user to a database role

12 / 25

12. In Transact-SQL, what statement is used to remove a user from a database role?

REMOVE USER FROM ROLE

REMOVE ROLE FROM USER

ALTER USER

ALTER ROLE

13 / 25

13. In SQL Server Management Studio (SSMS), what is the purpose of the “Schema Permissions” node in the “Securables” tab?

It provides a list of user logins.

It displays a list of all securables in the database.

It allows you to view the effective permissions of a schema.

It simplifies the process of creating new schemas.

14 / 25

14. In Transact-SQL, what is the purpose of the “DENY” statement when configuring permissions?

To grant permissions to a user or role

To assign roles to users

To explicitly deny specific permissions to a user or role

To create a new schema

15 / 25

15. In Transact-SQL, what statement is used to enable or disable the ownership chaining feature for a database?

ALTER ROLE

ALTER USER

ALTER DATABASE

ENABLE CHAINING

16 / 25

16. What is the primary benefit of using database roles in SQL Server for managing security?

Roles are used to define database schemas.

Roles allow you to create new schemas.

Roles simplify the management of permissions by grouping users.

Roles provide ownership of securables.

17 / 25

17. In SQL Server Management Studio (SSMS), what is the purpose of the “Server Roles” node in Object Explorer?

To provide a list of user logins

To design table schemas

To manage server-wide permissions

To create new databases

18 / 25

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

db_datareader

db_owner

public

db_datawriter

19 / 25

19. In Transact-SQL, which statement is used to grant a user the ability to grant the same permission to others?

GRANT OPTION

GRANT TO

GRANT WITH GRANT OPTION

GRANT USER

20 / 25

20. In SQL Server Management Studio (SSMS), what is the purpose of the “Ownership Chaining” option in database properties?

To control permissions inheritance

To adjust hardware configurations

To set the database collation

To configure server-wide settings

21 / 25

21. What is the primary benefit of using the “Effective Permissions” feature in SQL Server Management Studio (SSMS)?

It displays a list of server-wide settings.

It allows you to change database schemas.

It simplifies the process of assigning and revoking permissions.

It provides a list of all user logins.

22 / 25

22. In SQL Server Management Studio (SSMS), where can you find the “Effective Permissions” feature?

Solution Explorer

Object Explorer

Database Properties

Query Designer

23 / 25

23. In Transact-SQL, what is the primary benefit of using the “GRANT OPTION FOR” clause when granting permissions?

It provides ownership of the securable.

It grants the permission to execute stored procedures.

It allows the user to create new schemas.

It enables the user to grant the same permission to others.

24 / 25

24. In Transact-SQL, what statement is used to remove a previously granted permission from a user or role?

REVOKE ALL ON Securable FROM User

REVOKE ALL FROM User ON Securable

REVOKE PERMISSION

REMOVE PERMISSION

25 / 25

25. In SQL Server Management Studio (SSMS), what is the primary purpose of the “Securables” tab when configuring permissions?

To write T-SQL scripts

To specify the server name

To select the objects on which permissions are set

To define login credentials

Your score is

MS SQL Server – 47 – Managing Database Security MCQ