Dealing with orphaned users in SQL

SQL Server logins access individual databases using a database user that is mapped to the SQL Server login. There are two exceptions to this rule:

• The guest account. This is an account that, when enabled in the database, enables SQL Server logins that are not mapped to a database user to enter the database as the guest user.

• Microsoft Windows group memberships. A SQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

Finding Orphaned Users

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

Outputs a list of users in the selected database that are not linked to any SQL Server login.

N.B.	sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

Fixing orphaned users

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

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

• See books online Troubleshooting Orphaned Users which is where the above information was copied from.

Firing MDS Business Rules on dependent entities

I posted a question on the MSSQL MDS forum about

I have come across a few issues around business rules in MDS that look like they could be bugs but haven't been able to find anyone else online with similar problems...

I have a Customer Entity which is the upper level of my Customer hierarchy. At the lower level I have a Customer Store Location Entity which has domain based attributes of the Customer and Store Location. I've configured a business rule to set the Customer Store Location Name to be a concatenated string of the 'Customer Name' + ' - ' + 'Store Location' attributes which is set to trigger when the Customer Name attribute changes.

When editing the Customer entity at the top level, the Customer Store location Entity shows this change but even when running the business rules at both levels, the Customer Store Location Name does not update.

My two questions are:

1. Why is the change to Customer not picked up by the Customer Store Location entity's business rule even though I can see the attribute has changed?
2. Is there any way of triggering business rules to run for multiple entities if a change is made?

Suzanne Selhorn posted the following response which has done the trick

I think I have this figured out-- you need two rules.

Step 1: Set Customer Name attribute to be in a change tracking group (1).

Step 2: For CustomerStoreLocation entity, the first rule is: If has changed in group 1, then Customer = dba.Customer.Name.

Step 3: For CustomerStoreLocation entity, the second rule is: concatenate CustomerStoreLocation Name by using your two values. This doesn't need a "has changed" aspect to it, because you always want it to be true. So this rule is an action only, no condition.

Let me know if you need more details. If you run these two rules in order you should be good I think.