Which would be the best solution?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com also has a Windows group named
ABC\Employees that makes use of ABC_Prod. Some of the users in that group are members for
the db_owner role. However, one member in the ABC\ Employees group named Mia is not
allowed to access the SQL Server instance. You have received instructions from the CEO to make
sure that the Mia does not access the SQL Server instance. You need to complete this task using
the appropriate Transact-SQL statement.
Which would be the best solution?

You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Prod. ABC.com also has a Windows group named
ABC\Employees that makes use of ABC_Prod. Some of the users in that group are members for
the db_owner role. However, one member in the ABC\ Employees group named Mia is not
allowed to access the SQL Server instance. You have received instructions from the CEO to make
sure that the Mia does not access the SQL Server instance. You need to complete this task using
the appropriate Transact-SQL statement.
Which would be the best solution?

A.
You should consider using the following:
EXEC dbo.sp_droprolemember ‘db_master’, “ABC\Mia”;

B.
You should consider using the following:
DROP LOGIN “ABC\Mia”;

C.
You should consider using the following:
CREATE LOGIN “Mia\Mia” FROM Windows;
DENY CONNECT SQL TO ” Mia\Mia “;

D.
You should consider using the following:
CREATE LOGIN “ABC\Mia” FROM Windows;
DENY CONNECT SQL TO “ABC\Mia “; *

E.
You should consider using the following:
REMOVE LOGIN “ABC\Mia”;

Explanation:

CREATE LOGIN
Creates a new SQL Server login.
Option: WINDOWS
Specifies that the login be mapped to a Windows login.
DENY
Denies a permission to a principal. Prevents that principal from inheriting the permission through
its group or role memberships.



Leave a Reply 1

Your email address will not be published. Required fields are marked *


yuri

yuri

Is answer ‘C’ really better than answer ‘D’ ?
I think, the domain-name is ‘ABC’, so the name of Mia is ‘ABC\Mia’ -> ‘D’ should be correct !