List all Orphan users in a SQL Server database

CREATE TABLE ##ORPHANUSER (
DBNAME VARCHAR(100)
,USERNAME VARCHAR(100)
,CREATEDATE VARCHAR(100)
,USERTYPE VARCHAR(100)
)
EXEC SP_MSFOREACHDB ' USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE 
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN''
WHEN ISNTGROUP = 1 THEN ''NT GROUP''
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN''
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)'
SELECT *
FROM ##ORPHANUSER
DROP TABLE ##ORPHANUSER

Preventing logins and users to see metadata in SQL Server

To hide databases to all LOGINS, remove/revoke "VIEW ANY DATABASE" permission from the public server role

USE master;
GO

REVOKE VIEW ANY DATABASE
 TO PUBLIC;

To allow only some logins to view all databases, Jus create a user-defined server role

USE master;

CREATE SERVER ROLE [DBViewer];
GO

GRANT VIEW ANY DATABASE
 TO [DBViewer];

ALTER SERVER ROLE [DBViewer] ADD MEMBER [MyLogin];

This code creates a server role named DBViewer and grants the
VIEW ANY DATABASE permission to it. It then adds the login MyLogin to it.

Note: MASTER and TEMPDB will always be visible to all logins,We cannot make them invisible.

Script to sync up logins and database users in SQL Server

USE DATABASE
GO

EXEC sp_change_users_login 'update_one'
 ,'databse username'
 ,'loginname'
GO

Enable or Disable Logins with Stored Procedure

create procedure toggle_login (@login_name nvarchar(125), @set_enable bit)
as
begin
declare @sql_command nvarchar(500)
set @sql_command = 'ALTER LOGIN [' + @login_name + '] ' + case when @set_enable = 1 then 'ENABLE' else 'DISABLE' end
print @sql_command
exec sp_executesql @sql_command
end

 
--usage of above stored procedure

exec toggle_login 'domain\loginname,0