SQL Security : Restore User Mapping Database & SQL Management relation

To detect orphaned users, execute the following Transact-SQL statements per database:

USE <database_name>;GO;sp_change_users_login @Action=’Report’;GO;

Possible Response

The output lists the users and corresponding security identifiers (SID) in the current database that are not linked to any SQL Server login. For more information, see sp_change_users_login (Transact-SQL).

To repare the broken relation between sql user and db user you have to create the sql user . After this use the following procedure to recreate the relationship.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO
 

Example:

sp_change_users_login @Action=’update_one’, @UserNamePattern=’T-PrintAccounting’, @LoginName=’ T-PrintAccounting”;

You need possible a change of the user password. To do this use

USE master
GO
sp_password @old=NULL, @new='password', @loginame='<login_name>';
GO