You are the database administrator of your company. The network contains two instances of SQL Server 2008 named SQL1 and SQL2 . You move a database named ProdDB from SQL1 to SQL2 . A database user in the ProdDB database reports that he is unable to log in to SQL2 . You investigate anddiscover that the user has become orphaned. You want to detect all orphaned users in the ProdDB database on SQL2 . Which code should you execute?

A.
USE ProdDB;GO
EXEC sp_change_users_login
@action=’Report’,
@UserNamePattern= ‘user’, @LoginName=’login’; GO

B.
USE ProdDB; GO
sp_change_users_login @Action=’Report’; GO

C.
USE ProdDB; GO
EXEC sp_helplogins; GO

D.
USE ProdDB;
GO
EXEC sp_helplogins ‘Report’; GO

Explanation:

You should execute the following code:
USE ProdDB; GO
sp_change_users_login @Action=’Report’; GO
A database user requires a valid SQL Server login to log in to an instance of SQL Server 2008. A database usercan become orphaned if you restore, attach, or copy a database from one SQL Server instance to another. Youcan use the sp_change_users_login system stored procedure to detect orphaned users in a database. The @Action parameter of the sp_change_users_login system stored procedure defines the action that should beperformed by the procedure. Specifying the Report value for the @Action parameter displays a list of users andtheir security identifiers (SID) in the specified database that are not linked to any SQL login. You should not run the following code:
USE ProdDB;GO
EXECsp_change_users_login
@action=’Report’,
@UserNamePattern= ‘user’,
@LoginName =’login’;GO
This code is syntactically incorrect. You should not run the following code:
USE ProdDB; GO
EXEC sp_helplogins GO
This code provides information about SQL logins and users that are associated with the logins in the specifieddatabase. If you do not specify a particular database, information about SQL logins in all databases is displayed.
You should not run the following code:
USE ProdDB; GO
EXEC sp_helplogins ‘Report’; GO
The Report parameter is not a valid parameter for the sp_helplogins system stored procedure. Also, the sp_helplogins system stored procedure provides information about SQL logins and users that are associatedwith those logins in the specified database, not orphaned accounts. After generating a list of the orphaned accounts, you can then go to the original instance and create a script thatwill create the SQL logins. After creating the script, run the script on the new instance to create the accounts. Another possible solution is to transfer the logins to the other SQL Server 2008 instance. However, keep in mindthat transferred logins will no longer work on the original instance. The Transfer Logins task of SQL ServerIntegration Services (SSIS) will transfer the logins between SQL Server 2008 instances.

Objective:
Managing SQL Server Security

Sub-Objective:
Manage logins and server roles.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Troubleshooting > TroubleshootingConcepts > Troubleshooting Orphaned Users TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > ManagingServers > Managing Metadata When Making a Database Available on Another Server Instance