Sql-server – ny reason I shouldn’t delete orphaned database users

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:

SELECT p.name 
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
AND NOT EXISTS
(
  SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
);

If you need to do this for all databases, you can generate this dynamically, e.g.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT ''' + db.name + ''',p.name
  FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
  WHERE [type] IN (N''U'', N''G'')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
  );'
FROM sys.databases AS db
WHERE [state] = 0;

EXEC sp_executesql @sql;

As I understand it, this is because the SID's of the logins don't match between our two environments.

You understand correctly!

How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward? My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' but this has the disadvantages of needing to drop the logins first, and I'm not entirely sure that manually defining an SID is healthy / wise.

If the logins are already created, you'll have to drop them anyway - so that's a moot point. You can use the SSIS migrate logins task, sp_helprevlogin, CREATE LOGIN WITH SID =, and lastly you can make the database partially contained.

Maybe there is a way to have the logins migrated with the backup? (if sp_help_revlogin has been available to manually copy logins, why don't backups have this option)

Logins are a server scoped principal and can have a 1 to many relationship with users. Thus it doesn't make sense to include a server scope item with a database scoped item, that's why users exist. What you're looking for is called partially contained databases, which let a special type of user exist which has a passed and can be used like a login though it lives in the database and not at the server level. It's only good for that database and there are some requirements.

https://msdn.microsoft.com/en-us/library/ff929071.aspx